How to Use VLOOKUP in Excel (with Examples)
VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row. The formula is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Here is exactly how each part works.
VLOOKUP is the function people reach for when they need to match data between two tables — like pulling a price from a product list into an order sheet. It looks scary at first, but it only has four parts, and once you see them named, it clicks.
The VLOOKUP syntax, explained
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Part | Meaning |
|---|---|
| lookup_value | What you are searching for (e.g., a product code) |
| table_array | The table to search — the value must be in its first column |
| col_index_num | Which column number to return the answer from |
| range_lookup | FALSE for an exact match; TRUE for an approximate match |
A real example
Say column A has product codes and column B has prices, in the range A2:B100. To find the price of the code in cell D2, you write:
=VLOOKUP(D2, A2:B100, 2, FALSE)This searches column A for the code in D2, and returns the value from column 2 (prices). The FALSE at the end is the part most people forget — it forces an exact match. Leave it out and Excel does an approximate match, which returns wrong prices unless your list is sorted.
Common VLOOKUP errors
- #N/A — the value was not found. Check for extra spaces or a number stored as text.
- Returns the wrong value — you probably left off FALSE, or your lookup value is not in the first column of the table.
- #REF! — the column number is larger than the table has columns. See how to fix the #REF! error.
Should you use XLOOKUP instead?
If your Excel has XLOOKUP (Microsoft 365 and Excel 2021+), it is usually better: it can look left as well as right, does an exact match by default, and does not break when you insert columns. VLOOKUP is still everywhere, though, so it is worth knowing both. Once you are comfortable, combine it with pivot tables to build fast, self-updating reports.
Frequently asked questions
Why does my VLOOKUP return #N/A?
The lookup value was not found in the first column. The usual causes are trailing spaces, a number stored as text (or vice versa), or the value simply not being there. Wrapping the lookup in TRIM or matching the data types usually fixes it.
Can VLOOKUP look to the left?
No — VLOOKUP can only return a column to the right of the lookup column. To look left, use INDEX/MATCH or XLOOKUP, which do not have that limitation.