Excel VLOOKUP Function: Syntax, Examples and Common Errors
Finds a value in the first column of a table and returns something from a column to its right.
VLOOKUP answers one question: given a value I already have, what is the matching value in another column? You have a product name and you want its price; you have an employee ID and you want their department. VLOOKUP walks down the first column of a table until it finds your value, then jumps across to the column you asked for and returns whatever is sitting there.
The V is for vertical — it searches down a column, not across a row. That single design decision is behind almost every complaint about VLOOKUP: the value you are searching for has to be in the leftmost column of the range you hand it, and the column you want back has to be to the right of it. When your data does not sit that way round, VLOOKUP cannot help you and you need INDEX+MATCH or XLOOKUP instead.
It is still worth learning properly. VLOOKUP is in millions of existing spreadsheets, it is the function interviewers ask about, and on a table that is laid out the right way round it is shorter and clearer than the alternatives.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Arguments
lookup_valueRequired- The value to search for. It is matched against the first column of table_array and nothing else.
table_arrayRequired- The range to search. Its first column must be the one containing lookup_value. Lock it with absolute references ($A$2:$D$5) if you plan to fill the formula down.
col_index_numRequired- Which column of table_array to return, counted from 1 at its left edge — not from column A of the sheet.
range_lookupOptional- FALSE for an exact match, TRUE for an approximate one. It defaults to TRUE, which is almost never what you want — always pass FALSE unless you are deliberately banding values.
The example data
Headers in row 1, data in A2:D5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Stock | Price |
| 2 | Cordless drill | Power tools | 24 | 89.99 |
| 3 | Extension lead | Electrical | 140 | 12.5 |
| 4 | Safety goggles | Safety | 68 | 7.25 |
| 5 | Work gloves | Safety | 210 | 5.4 |
Worked examples
=VLOOKUP("Safety goggles", A2:D5, 4, FALSE)Result: 7.25
Finds Safety goggles in column A, then returns the 4th column of A2:D5 — Price.
=VLOOKUP("Work gloves", A2:D5, 2, FALSE)Result: Safety
Same search, different column. Column 2 of A2:D5 is Category, because counting starts at the range's left edge.
=VLOOKUP(A3, $A$2:$D$5, 3, FALSE)Result: 140
Looking up a cell reference rather than typed text, so the formula can be filled down. The $ signs stop the table drifting as it goes.
=IFERROR(VLOOKUP("Hammer", A2:D5, 4, FALSE), "Not stocked")Result: Not stocked
Hammer is not in the table. Wrapping the lookup in IFERROR turns the #N/A into something a reader can act on.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#N/A
Why it happens: The value genuinely is not in the first column — or it is, but not in a form that matches. Trailing spaces, a number stored as text, and a different capitalisation of the same word all read as 'not found'.
How to fix it: Wrap the lookup value in TRIM() to kill stray spaces, and check whether one side is text and the other a number. If a missing value is expected, wrap the whole thing in IFERROR.
#REF!
Why it happens: col_index_num is larger than the number of columns in table_array — asking for column 5 of a four-column range.
How to fix it: Count the columns in your range again, starting at 1 on its left edge. This often appears after someone deletes a column inside the table.
#VALUE!
Why it happens: col_index_num is less than 1, or is text rather than a number.
How to fix it: Make sure the third argument is a positive whole number.
Wrong value returned, no error
Why it happens: range_lookup was left out, so it defaulted to TRUE and returned the closest match below your value instead of an exact one.
How to fix it: Always pass FALSE as the fourth argument. This is the single most common VLOOKUP bug and it is silent — the sheet looks fine and the numbers are wrong.
Tips worth knowing
- Lock the table with absolute references ($A$2:$D$5) before filling the formula down, or every row will search a range one row lower than the last.
- Inserting a column inside the table shifts what col_index_num points at, but does not update the number. INDEX+MATCH and XLOOKUP do not have this problem.
- To make col_index_num self-maintaining, replace the hard-coded number with MATCH on the header row.
- If your lookup column is not the leftmost one, stop — VLOOKUP cannot look left. Use XLOOKUP or INDEX+MATCH.
Frequently asked questions
Why does my VLOOKUP return #N/A when I can see the value in the table?
Almost always a formatting mismatch rather than a missing value. The usual culprits are a trailing space in one of the two cells, or a number stored as text on one side and as a real number on the other. Test it with =A2=D2 — if that returns FALSE for two cells that look identical, you have found the problem.
Can VLOOKUP look to the left?
No. It only ever returns a column to the right of the one it searched. If the value you want sits to the left of your lookup column, use INDEX+MATCH or XLOOKUP, both of which can return a value in any direction.
What is the difference between TRUE and FALSE in the last argument?
FALSE demands an exact match and returns #N/A if there isn't one. TRUE returns the largest value less than or equal to what you searched for, and requires the first column to be sorted ascending. TRUE is only useful for banding — tax brackets, grade boundaries, shipping tiers. For everything else, pass FALSE.
Should I still use VLOOKUP or switch to XLOOKUP?
Use XLOOKUP for new work if your Excel has it — it looks in both directions, defaults to an exact match, and survives column insertions. Keep VLOOKUP for sheets that have to open in older versions, and learn it regardless, because you will meet it in other people's files.
Related functions
- XLOOKUPSearches any range and returns a value from any other, in either direction, with a built-in fallback.
- INDEX MATCHMATCH finds the position of a value; INDEX returns what is at that position. Together they look up anything, in any direction.
- IFERRORRuns a formula and returns your own value instead if it produces an error.
Guides that use it
Longer reads where this function does real work in a real sheet.
- Nine Ways a Cell Can Say No: Excel's Error Values, IFERROR, IFNA and the 2,952.75 Somebody Turned Into a Zero
- Absolute, Relative and Mixed References: The Dollar Signs That Decide What Fills Down
- INDEX and MATCH: The Lookup Pair That Survives a Moved Column
- XLOOKUP vs VLOOKUP: When to Use Each (and How to Switch)