VLOOKUP not working? 9 causes and how to fix each one
VLOOKUP returning #N/A even though the values match, the wrong value, or breaking when you drag it down? The 9 usual causes and the exact fix for each.
Paste any Excel formula and read it in plain English, one step at a time from the inside out, with the usual mistakes flagged and a lesson for each function. Free, and nothing leaves your browser.
100% in your browser: your data never leaves your device
Start at the innermost brackets and work outwards, because that is the order Excel works it out in. Each function hands its answer to the one around it. Take this lookup, which finds the price of P-103:
=INDEX(C2:C5, MATCH("P-103", A2:A5, 0)) →2.25
Read from the inside: MATCH("P-103",A2:A5,0) looks down the codes and finds P-103 in third place, so it gives 3. Then INDEX(C2:C5, 3) takes the third price, 2.25. The explainer above numbers the steps the same way and lights up each part of the formula
as you point at its step.
Each function takes its inputs in a fixed order, separated by commas. For VLOOKUP that's what to look for, where to look, which column to bring back, and whether the match must be exact:
=VLOOKUP(A2,$D$2:$F$50,3,FALSE) Square brackets in a function's help, as in [range_lookup], mean the argument can be left out. Leaving one out isn't always harmless:
without FALSE, VLOOKUP does an approximate match. The VLOOKUP lesson shows what goes wrong.
Usually a range without $ signs. Fill =VLOOKUP(A2,D2:F50,3,FALSE) down one row and the table becomes D3:F51; a few rows later the first rows of the table are no longer in it. Lock the range as $D$2:$F$50 by pressing F4. The
explainer flags ranges like this, and the locking the table range lesson lets you watch one slide.
For a whole formula that's still wrong, why VLOOKUP is not working goes through the other causes.
VLOOKUP returning #N/A even though the values match, the wrong value, or breaking when you drag it down? The 9 usual causes and the exact fix for each.
How INDEX MATCH works in Excel, step by step: look up to the left, match on two or more criteria, two-way lookups, and fixes for #N/A and wrong rows.
The Excel formulas every beginner needs, in the order to learn them: +, SUM, AVERAGE, COUNT, ROUND, percentages, IF and $. Each with an example to practise.
Yes, free with no sign-up. Paste a formula and the explanation appears as you type. It runs in your browser, so the formula is never sent anywhere.
Over 90 of the functions people use most: lookups (VLOOKUP, XLOOKUP, INDEX and MATCH), IF, IFS and IFERROR, SUMIFS and COUNTIFS, text, date and dynamic array functions such as FILTER and UNIQUE. A function it doesn't know is flagged, and the rest of the formula is still explained.
Because that is how Excel works it out: the innermost function first, then the one around it, using that result. In =INDEX(C2:C5,MATCH("P-103",A2:A5,0)) MATCH runs first and INDEX uses its answer.
VLOOKUP or MATCH left on approximate match, ranges without $ that slide when the formula is filled down, IFERROR hiding errors it shouldn't, a column number past the end of the table, SUMIFS ranges of different sizes, division that can give #DIV/0!, and IFs nested three deep.
Yes, for the functions both share, which is most of them. Formulas written with semicolons between arguments, as in many European versions of Excel, work too.
Yes. The formula is kept in the page address, so Copy link gives a link that opens the same explanation. It is stored after the # sign, which browsers never send to the server.
No: it explains what the formula does, not the answer, because it doesn't have your data. To see results, the lessons let you type formulas into a spreadsheet in your browser and check them.
Know someone fighting with a spreadsheet? Send them this page.