VLOOKUP in Excel

VLOOKUP in Excel is a formula that matches a value against the first column of a range and returns a value from another column in the same row. It's one of the most widely used formulas in Excel because so many real spreadsheets involve matching two lists together - customer IDs to names, product codes to prices, order numbers to status.

This page covers how the formula works, a complete worked example, and the mistakes that most often stop it from returning the value you expect.

1. Excel takes the lookup_value and scans down the first column of table_array.
2. When it finds a row where that column matches exactly (with FALSE set), it stops scanning.
3. It then moves right by col_index_num columns, counting the first column of table_array as 1.
4. The value in that column, on the matching row, is returned as the result.

This order table is used to look up the Status of ORD-502:

Order ID is column A (the leftmost, required position), and Status is the fourth column across, so col_index_num is 4.

Leaving range_lookup as TRUE, or omitting it, tells VLOOKUP to look for the closest value less than or equal to lookup_value, which requires the lookup column to be sorted ascending. This is useful for things like tax brackets or grading scales, but it's the wrong setting for matching IDs, where FALSE and an exact match is what's needed.

A VLOOKUP can return a value with no error shown, yet still be wrong, if range_lookup was left as TRUE against unsorted data. Excel won't flag this as a mistake - it will confidently return whatever the nearest match happens to be, so it's worth double-checking FALSE is set whenever exact IDs are involved.

Turbo Excel VLookup applies this same matching logic across an entire dataset at once. You choose the matching columns and the columns to pull across through dropdown menus, and it runs the match for every row without needing the formula copied down manually.

A worksheet with thousands of VLOOKUP formulas recalculating against a large table_array can slow Excel down noticeably, especially if the range isn't locked and Excel has to re-evaluate a shifting reference on every keystroke.

Converting the source range into an Excel Table (Insert > Table) before referencing it in VLOOKUP tends to help, since Table references automatically resize with the data and stay consistent without needing manual $ locking.

Before applying a new VLOOKUP formula to an entire column, it's worth testing it on a single row and manually verifying the result against the source data, since a mistake caught in one cell takes seconds to fix, while the same mistake copied down five hundred rows takes much longer to untangle.

This is especially worth doing whenever the source table_array has changed shape recently, since a resized or reordered table is the single most common reason a previously reliable formula starts returning unexpected results.