Knowledge base / Excel
VLOOKUP in Excel: syntax, examples and pitfalls
I show how VLOOKUP finds a value in the first column of a table, why I always type the fourth argument myself and what breaks the formula when someone inserts a column.
VLOOKUP is where many people start joining tables in Excel. I have an ID in one place and need a price, a name or a category from a price list. The function finds that ID in the first column of the table I point to and returns a value from the same row.
It works in every version of Excel, so you will find it in old reports and in files from clients. It also has two traps that can produce a result that looks fine and is wrong. I cover both below. A larger example with orders, duplicates and a comparison of three lookup functions is in the article VLOOKUP, HLOOKUP and XLOOKUP: one practical Excel example.
VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])lookup_value: what I am looking for, for example a product ID or a reference to the cell that holds it.table_array: the range whose first column contains the lookup value. The result column has to be to the right of it.col_index_num: the number of the result column, counted from the left edge of the range, not from column A of the sheet.range_lookup:FALSE(or0) means an exact match,TRUE(or1) an approximate match. If you leave it out, Excel usesTRUE.
The argument separator comes from regional settings. Many European locales use a semicolon instead of a comma, and localised Excel versions also translate function names, so a formula copied from a tutorial may need different separators and, in a localised Excel, translated names.
Example: a product price by ID
I use a small, synthetic price list on a sheet called Products, in the range A1:D6:
| ID | Product | Category | Price |
|---|---|---|---|
| P-101 | Mug | Home | 24.90 |
| P-102 | Notebook | Office | 12.50 |
| P-103 | Lamp | Home | 89.00 |
| P-104 | Pen | Office | 4.20 |
| P-105 | Backpack | Travel | 149.00 |
I want the price of product P-103:
=VLOOKUP("P-103", Products!$A$2:$D$6, 4, FALSE)The result is 89.00. Excel found P-103 in the first column of the range and returned the value from the fourth column of that range, which is Price.
In a real file I do not type the ID into the formula. It sits in a cell next to it, for example B2 on the orders sheet:
=VLOOKUP(B2, Products!$A$2:$D$6, 4, FALSE)The $ signs lock the price-list range. Without them, filling the formula down would shift the range as well, and each next row would search a smaller part of the list. That is a common reason why a formula works in the first row and returns #N/A in the tenth.
Two details about matching text are worth knowing early. VLOOKUP does not distinguish upper and lower case, so p-103 also finds P-103. With an exact match it supports wildcards: ? stands for any single character and * for any sequence of characters. If the text you look for contains a literal asterisk or question mark, put a tilde ~ in front of it.
Exact and approximate match
I always type the fourth argument myself. The default TRUE assumes the first column is sorted in ascending order and matches the row with the largest key less than or equal to the lookup value. With IDs I almost never want that. On an unsorted list, a formula without the fourth argument can return a value from another row without any error message.
Approximate match does have a sensible use: thresholds. Take a synthetic discount table in F1:G5, sorted in ascending order by the first column:
| From amount | Discount |
|---|---|
| 0 | 0% |
| 500 | 3% |
| 1000 | 5% |
| 5000 | 8% |
=VLOOKUP(1200, $F$2:$G$5, 2, TRUE)The result is 5%, because 1000 is the largest threshold that does not exceed 1200. For an amount of 499.99 I get 0%, and 5000 already returns 8%. A negative amount, smaller than the first threshold, returns #N/A. A threshold added below the table also needs a wider range in the formula, and one inserted out of order can make the results drift. So next to tables like this I add a short note: sort ascending and check the range.
Errors and what they tell me
| Error | What it usually means |
|---|---|
| #N/A | The value is missing from the first column. Or it is there with a different type: 101 as a number and “101” as text are two different values for Excel. |
| #REF! | col_index_num is greater than the number of columns in the range, for example 5 with the range A:D. |
| #VALUE! | col_index_num is less than 1. |
The most dangerous outcome has no error at all: a value from the wrong row or the wrong column. After writing the formula I check two or three rows by hand, including one whose key I know is missing from the lookup table.
I handle a missing match on purpose. If a product may not exist in the price list, I say so in the result instead of hiding it:
=IFNA(VLOOKUP(B2, Products!$A$2:$D$6, 4, FALSE), "not in price list")I choose IFNA over IFERROR because it catches only #N/A. A #REF! caused by a wrong column number stays visible instead of disappearing behind “not in price list”.
The column number is the weakest part of the formula
Suppose someone inserts a new Stock column between Category and Price. Excel expands the range in the formula to $A$2:$E$6, but the number 4 stays the same. The formula starts returning stock levels instead of prices. No error appears; only the numbers change.
I have two ways to deal with it. The first keeps VLOOKUP and calculates the column number from the header with MATCH:
=VLOOKUP(B2, Products!$A$2:$E$6, MATCH("Price", Products!$A$1:$E$1, 0), FALSE)The second is XLOOKUP, where I point to the key column and the result column separately. When a column is inserted, Excel moves the reference together with the Price column, so the formula keeps returning the price. This is how it looks after the change, with the price now in column E:
=XLOOKUP(B2, Products!$A$2:$A$6, Products!$E$2:$E$6, "not in price list")When I choose a different function
I keep VLOOKUP in files that must work in older Excel versions and in existing reports that work correctly. I do not rewrite them just to use a newer function name.
In a new file I use XLOOKUP if everyone who opens it has it. According to Microsoft it is available in Microsoft 365, Excel 2021 and 2024 and Excel for the web, among others, and not in Excel 2016 or 2019. It can look to the left, has a built-in value for a missing match and searches for an exact match by default.
When a file has to work everywhere and I need a left lookup, I combine INDEX with MATCH. The same idea for data laid out in rows instead of columns is HLOOKUP.
A short exercise
Copy the price list from the example into a sheet and add three IDs next to it: P-102, p-104 and P-110. Write an exact-match formula and check which result is a price and which one is a missing value. Then insert a column between Category and Price and see what happened to the results. Finally, fix the formula with one of the two approaches above and make sure the prices are back where they belong.
Key takeaways
Why does VLOOKUP return #N/A when I can see the value in the table?
Usually the keys differ in type or characters: a number stored as text, an extra space or a different dash. VLOOKUP does not distinguish upper and lower case, so letter case is rarely the reason.
What happens if I leave out the last VLOOKUP argument?
Excel uses TRUE, an approximate match that expects the first column sorted in ascending order. For IDs and names I always pass FALSE to get an exact match.
Can VLOOKUP return a value from a column to the left?
No. It always searches the first column of the range and returns a value from a column to its right. For a left lookup I use XLOOKUP or INDEX with MATCH.
