How to Use VLOOKUP in Excel (and Fix #N/A)
· 6 min read
To use VLOOKUP, tell it what to find, which table to search, which column number to return, and FALSE for an exact match. It searches the first column of the table, finds the row, and returns the value from the column you asked for:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)The last argument is the one people forget. Without FALSE, VLOOKUP does an approximate match and can return a wrong value with no error at all. VLOOKUP works in every version of Excel.
The example table
A cleaning supplies price list. Codes are in the first column, which is where VLOOKUP always searches. The codes to look up are in F2 and F3, and column H has the VLOOKUP formulas from this guide.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Pack | Price | Find | Formula | Result | |
| 2 | C-410 | Bin bags | Roll of 50 | 7.80 | C-413 | Price of F2 (column 4) | 11.40 | |
| 3 | C-411 | Disinfectant spray | 500 ml | 4.60 | C-416 | Product of F2 (column 2) | Microfiber cloths | |
| 4 | C-412 | Mop head | Single | 9.25 | Missing code F3 | #N/A | ||
| 5 | C-413 | Microfiber cloths | Pack of 10 | 11.40 | Column 5 of a 4-column table | #REF! | ||
| 6 | C-414 | Hand soap | 1 L | 6.30 |
How to use VLOOKUP, step by step
You want the price of the code typed in F2, which is C-413.
- lookup_value: the thing you know. Here,
F2. - table_array: the whole table, starting with the column you search. Here,
A2:D6. - col_index_num: which column of that table to return, counting from 1. Price is the 4th column of A to D, so
4. - range_lookup:
FALSEfor an exact match.
=VLOOKUP(F2, A2:D6, 4, FALSE)Result: 11.40. Change the column number to 2 and the same formula returns Microfiber cloths. Both are in the sheet above, in H2 and H3.
Filling it down
When you copy the formula down a list of codes, lock the table so it does not move:
=VLOOKUP(H2, $A$2:$D$6, 4, FALSE)H2 changes to H3, H4 and so on, but the price list stays at A2:D6. If you forget the $ signs, each copy looks at a table that starts one row lower, and the last rows start returning #N/A. Our guide to absolute references explains how to add them with F4.
VLOOKUP exact match vs approximate match
The fourth argument decides how VLOOKUP matches:
| range_lookup | What it does | Use it for |
|---|---|---|
FALSE or 0 | Exact match only, #N/A if missing | Codes, IDs, names: almost everything |
TRUE, 1 or left out | Largest value less than or equal to the lookup value | Sorted bands, like discount tiers |
Here is why leaving it out is dangerous. Type a code that does not exist, C-416, and drop the last argument:
=VLOOKUP("C-416", A2:D6, 4)Result: 6.30, the price of hand soap. Approximate match found the largest code that is not bigger than C-416 and returned its price. With FALSE you would get #N/A, which at least tells you the code is missing: that is H4 in the sheet above, where F3 holds C-416. On an unsorted list, approximate match can return values from rows that are not even close.
Approximate match is useful for real bands and tiers. We show that case in VLOOKUP vs XLOOKUP vs INDEX MATCH.
VLOOKUP not working: a checklist
When a VLOOKUP gives an error or a strange value, go through these in order.
1. The #N/A error: the value is not found
#N/A means VLOOKUP looked down the first column and did not find an exact match. Common causes:
- The value really is missing. Check the source list before you hide anything.
- Extra spaces. "C-413 " with a trailing space does not match "C-413". Compare
=LEN(F2)with the length you expect, or clean the column with TRIM. - Numbers stored as text. An order ID typed as the number 1001 does not match the text "1001". Text numbers are often left aligned and may show a small green triangle. Convert one side so both are the same type.
- The search column is not first. VLOOKUP only searches the leftmost column of
table_array. If your codes are in column B and the range starts at A, it searches the wrong column.
2. A wrong value with no error
- Missing
FALSE. See the example above. This is the most common cause. - The column number is out of date. Someone inserted a column. Say a "Supplier" column goes in between A and B. The table becomes A2:E6 and the formula still says
4, which now points at the Pack column. The same formula returns Pack of 10 instead of a price.
3. Other errors
- #REF!: the column number is bigger than the number of columns in the table.
A2:D6has 4 columns, so5fails (H5 in the sheet). - #VALUE!: the column number is less than 1.
Handling the vlookup n/a error on purpose
Once you know why some values are missing, you can show a friendlier result:
=IFNA(VLOOKUP(F2, A2:D6, 4, FALSE), "Not in list")IFNA only catches #N/A. IFERROR catches every error, including a broken formula, so it can hide real problems. More in IFERROR in Excel.
Common mistakes
Trusting an AI's VLOOKUP without reading the last argument. AI assistants often write something like =VLOOKUP(H2, A2:D6, 4) with no fourth argument. It returns a number in every row, so it looks finished. But it is an approximate match, and any code that is missing or out of order pulls a neighbour's price. When an assistant gives you a VLOOKUP, read the end of the formula first. If it does not say FALSE or 0, it is not an exact lookup.
Counting columns from the sheet, not the table. If the table starts at column C, then column C is 1, D is 2, and so on. Counting from column A gives the wrong column.
Forgetting the $ signs. The formula works in the first row and breaks further down. Click the last filled cell and read its range.
Wrapping it in IFERROR straight away. You lose the clue that tells you which codes are missing.
Using VLOOKUP where XLOOKUP is available. If everyone has Microsoft 365, Excel 2021 or Excel for the web, XLOOKUP is exact by default and does not use column numbers at all.
FAQ
How do I use VLOOKUP in Excel?
Write =VLOOKUP(value, table, column_number, FALSE). The value must be in the first column of the table, and the column number counts from that first column.
Why is my VLOOKUP not working?
Check four things: the value exists in the first column, there are no extra spaces, both sides are the same type (number or text), and the last argument is FALSE.
What does the #N/A error mean in VLOOKUP?
It means no exact match was found in the first column of the table. Look for missing values, spaces and numbers stored as text before you hide the error with IFNA.
How do I make VLOOKUP an exact match?
Put FALSE (or 0) as the fourth argument. If you leave it out, VLOOKUP uses approximate match.
Can VLOOKUP look to the left?
No. It only returns columns to the right of the search column. Use INDEX MATCH or XLOOKUP for that.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Prices pulled by the AI look odd” →