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.
A real-world use case: merging two tables
The classic job for VLOOKUP is pulling details from a lookup table into a working sheet. Suppose your orders sheet has product codes in column A, and a separate tab named Prices lists codes in column A and prices in column B. Next to the first order, enter this and copy it down:
=VLOOKUP(A2, Prices!A:B, 2, FALSE)Every order now shows the right price, and when a price changes on the Prices tab, all the orders update automatically. That single pattern — match a key, pull a detail — covers most of what VLOOKUP is used for.
Exact match vs approximate match
| Last argument | What it does | When to use |
|---|---|---|
| FALSE (or 0) | Exact match; returns #N/A if none | Almost always — codes, names, IDs |
| TRUE (or 1) | Closest value not greater than the key; needs sorted data | Rare — tax brackets, grade bands |
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.
VLOOKUP's limitations
- It only looks right — the return column must be to the right of the lookup column.
- It breaks when columns move, because the column number is hard-coded.
- The key must be in the first column of the range.
- It returns only the first match, not a list of all matches.
Should you use XLOOKUP or INDEX MATCH instead?
If your Excel has XLOOKUP (Microsoft 365 and Excel 2021+), it is usually better: it looks left as well as right, matches exactly by default, and does not break when you insert columns. On older versions, INDEX MATCH gives the same flexibility. VLOOKUP is still everywhere, though, so it is worth knowing all three. 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.
How do I VLOOKUP between two sheets?
Put the sheet name before the range, like =VLOOKUP(A2, Prices!A:B, 2, FALSE). The formula behaves exactly the same when the lookup table is on another tab.
What does the last argument in VLOOKUP mean?
It is the match mode: FALSE (or 0) forces an exact match, while TRUE (or 1) does an approximate match that needs the first column sorted. For everyday lookups, always use FALSE.