Featured image
VLOOKUP in Excel
VLOOKUP finds a value in the first column of a table and returns something from the same row of a column further right. Written in full it is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE), where the final FALSE asks for an exact match. Leave that last argument off and Excel looks for the nearest match instead, which on unsorted data returns a confidently wrong row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP is the function most spreadsheet work is actually built out of, and the one people most often describe as broken. It is rarely broken. Nearly every failure comes down to one of four things: the value being looked up is not in the first column of the range, the range moved when the formula was copied, the exact-match argument was left off, or the two values that should match differ by a space nobody can see.
The page works through the arguments one at a time, then three examples against a table you can edit below, then each error VLOOKUP returns and what causes it. Everything here runs in a real spreadsheet engine, so you can change a cell and watch the answer change rather than take the result on trust.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The thing you already know, such as an employee name or an order number. It can be typed into the formula in quotes, or, far more usefully, be a reference to a cell holding it.
- The whole block being searched, headers optional. VLOOKUP only ever looks for lookup_value in the leftmost column of this range, which is the single most important sentence on this page. If what you know sits to the right of what you want, VLOOKUP cannot do the job and XLOOKUP or INDEX with MATCH can.
- Which column of table_array to read the answer out of, counted from 1 at the left edge of the range rather than from column A of the sheet. A range of A2:D7 numbers its columns 1 to 4, so revenue in column D is 4.
- optional
- FALSE for an exact match, TRUE for the nearest match at or below the lookup value. Optional in Excel's syntax and not optional in practice: omit it and Excel assumes TRUE, which only behaves on a list sorted smallest to largest and silently misreports on anything else.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Find one person's revenue
=VLOOKUP("Chen", A2:D7, 4, FALSE)Returns 7600
Chen is searched for in column A, the leftmost column of A2:D7. Revenue is the fourth column of that range, so col_index_num is 4. FALSE asks for Chen exactly, not the closest thing to Chen.
Read a different column from the same row
=VLOOKUP("Dara", A2:F7, 6, FALSE)Returns B
Only two things changed: the range now reaches as far as column F, and the column number counts out to 6. Widening the range is what makes the extra column reachable at all, which is why a VLOOKUP that worked yesterday breaks when somebody inserts a column inside it.
Say something sensible when there is no match
=IFNA(VLOOKUP("Gus", A2:D7, 4, FALSE), "Not on the list")Returns Not on the list
There is no Gus in the table, so the VLOOKUP returns #N/A and IFNA replaces it. IFNA rather than IFERROR on purpose: it catches only the not-found case, so a genuinely broken formula still shows up as broken instead of being tidied away behind the same friendly message.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Exact match and approximate match
The fourth argument changes what VLOOKUP means by a match, and it is where most wrong answers come from, because a wrong answer here still looks like an answer.
- FALSE, or 0, means exact. VLOOKUP scans the first column from the top and stops at the first cell equal to lookup_value. If there is no such cell it returns #N/A, which is the honest outcome: nothing matched.
- TRUE, or 1, or the argument left off entirely, means approximate. VLOOKUP assumes the first column is sorted smallest to largest and finds the largest value at or below lookup_value. On an unsorted list it does not warn you; it returns whatever the assumption led it to.
Approximate match is not a mistake in itself. It is the right tool for a banded lookup: tax bands, commission tiers, grade boundaries, anywhere a number falls into a range rather than matching a row. For that, the first column holds the lower bound of each band, sorted ascending, and TRUE is exactly what you want.
When to reach for something else
VLOOKUP has two structural limits, and neither is a bug. It can only look rightwards from the first column of its range, and it addresses the answer by a column number that knows nothing about the column it is counting to.
=XLOOKUP(7600, D2:D7, A2:A7)Returns Chen. XLOOKUP takes the column to search and the column to return as two separate ranges, so looking leftwards is not a special case and inserting a column cannot break it.
=INDEX(A2:A7, MATCH(7600, D2:D7, 0))Returns Chen. INDEX with MATCH does the same job in every version of Excel ever shipped, which is why it is still worth knowing.
None of that makes VLOOKUP obsolete. It is shorter, it is in every workbook you will ever be handed, and on a table whose key really is the first column it is the clearest thing to write.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=VLOOKUP("Gus", A2:D7, 4, FALSE)WhyThe exact match found nothing. Either the value genuinely is not in the first column of the range, or the two values differ in a way the eye cannot see.
FixCheck three things in this order. First, that the lookup value really is in the leftmost column of table_array and not somewhere to its right. Second, that a number in one place is not text in the other, ISNUMBER down both columns settles it in seconds. Third, trailing spaces: wrap the lookup value in TRIM and see whether the error disappears.
=VLOOKUP(" Chen ", A2:D7, 4, FALSE)WhyThe same error from the most common invisible cause. There is a Chen in the table; there is no space-Chen-space.
FixTRIM strips the spaces at both ends, and =VLOOKUP(TRIM(" Chen "), A2:D7, 4, FALSE) returns 7600. If a whole imported column is affected, clean the column once rather than wrapping every formula.
=VLOOKUP("Chen", A2:D7, 9, FALSE)Whycol_index_num points past the right-hand edge of table_array. A2:D7 is four columns wide and the formula asked for the ninth.
FixCount the columns in the range rather than on the sheet. The number is 1 for the leftmost column of table_array, and the largest legal value is the width of the range. This error also appears when a range was narrowed after the formula was written.
=VLOOKUP("Chen", A2:D7, 0, FALSE)Whycol_index_num is less than 1. Counting from zero is the reflex of every programmer meeting Excel for the first time.
FixThe leftmost column of the range is 1, not 0. A #VALUE! here can also mean the column number arrived as text rather than a number.
What goes wrong
These are the mistakes that come up again and again with VLOOKUP. Each one is reasonable. That is why people make it.
Leaving the fourth argument off.
WhyIt is marked optional, the formula is shorter without it, and on small sorted test data it appears to work.
InsteadWrite FALSE every time unless you are deliberately doing a banded lookup. The failure mode of the default is not an error message, it is a plausible wrong number.
Counting col_index_num from column A of the sheet.
WhyThe column letters are right there on screen, and the range is not.
InsteadCount from the left edge of table_array. In =VLOOKUP(x, C2:F20, 2, FALSE) the 2 means column D, because C is 1.
Copying a VLOOKUP down a column without locking the table range.
WhyThe first row works, so the formula looks finished.
InsteadMake table_array absolute, $A$2:$D$7, so it stops sliding down the sheet. The lookup value should stay relative so it moves with each row. This is the single most common cause of a column of VLOOKUPs where the top few are right and the rest are #N/A.
Wrapping the whole thing in IFERROR to make the red go away.
WhyThe errors stop appearing, which feels like progress.
InsteadIFERROR catches #REF! and #VALUE! as readily as #N/A, so a formula pointing at the wrong column now returns a tidy blank instead of complaining. Use IFNA when what you mean is not found.
Common questions
Why does VLOOKUP return #N/A when I can see the value in the table?
Almost always because the two values are not actually equal. A trailing space, a number stored as text, or a non-breaking space pasted from a web page will all look identical on screen and compare as different. Wrap the lookup value in TRIM, and run ISNUMBER down both columns to check that one side is not text.
Can VLOOKUP look to the left?
No. It only ever searches the leftmost column of its range and reads rightwards from there. XLOOKUP takes the search column and the return column as separate arguments and has no such restriction; INDEX with MATCH achieves the same thing in older versions.
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP defaults to an exact match, takes the lookup and return ranges separately so it can look in either direction, and has a built-in argument for what to show when nothing is found. VLOOKUP is shorter, works in every version of Excel, and is what the workbooks you inherit are full of.
Is VLOOKUP case sensitive?
No. =VLOOKUP("chen", A2:D7, 4, FALSE) returns the same 7600 as the capitalised version. If capitalisation has to matter, test it separately with EXACT.
What does the FALSE at the end of a VLOOKUP mean?
It asks for an exact match. TRUE, or the argument left off, asks for the largest value at or below the one you are looking for, which only makes sense on a list sorted smallest to largest.
Does VLOOKUP work in Google Sheets?
Yes, with the same four arguments in the same order. The exercises here apply unchanged.