Excel VLOOKUP Function
excel-vlookup• Pick a product code and watch VLOOKUP find the value step by step. • Learn each argument (lookup value, range, column index, FALSE) by example.
💡 Change just the yellow cell (G2) and all three results update at once. The downloaded Excel file behaves the same way.
How to read VLOOKUP
“Take the lookup value, find it in the leftmost column of this table, and return the value from the Nth column of that row.”
Wrapped in IFERROR, a missing code shows “Not found” instead of an error (#N/A).
Where VLOOKUP usually trips people up
VLOOKUP is one of the most used functions in Excel, and the places people get stuck are fairly predictable. The first is the rule that the lookup value must sit in the leftmost column of the range. If the value you want back is to the left of the one you search on, VLOOKUP cannot do it — that is a job for INDEX/MATCH.
The second is locking the range. Dragging the formula down shifts the range with it, so the lower rows return wrong results. Writing the range as $B$2:$D$100 keeps it fixed no matter where you copy the formula.
The third is the last argument. Leave out the 0 (or FALSE) and Excel looks for an approximate match, which returns nonsense on an unsorted table. Always write the 0 when you want an exact match. The #N/A that appears when nothing is found can be wrapped in IFERROR to show a message of your choice.
Frequently asked questions
Why does VLOOKUP return #N/A?
It means the lookup value is not in the first column of the range. When the value is clearly there, one side usually carries a stray space, or one side is a number while the other is stored as text. Cleaning the text with TRIM and matching the formats fixes most cases.
How do I fix a #REF! error?
The third argument, the column index, is larger than the number of columns in the range. $A$2:$D$8 has four columns, so anything from 5 upward gives #REF!. Deleting a column the formula referred to causes the same error, so check the range and the index together.
What does #VALUE! mean here?
The column index is zero, negative, or not a number at all. It also shows up when an argument is omitted and everything after it shifts one place. Watching the index change between 2, 3 and 4 in the demo above makes the argument order easier to hold on to.
How do I show a blank or a 0 instead of an error when nothing is found?
Wrap the formula as =IFERROR(VLOOKUP(...),"") and the error is replaced by whatever you specify; put 0 in place of the quotation marks to show a zero. Hiding errors also hides genuinely broken data, so confirm the cause before you cover it up.
Can VLOOKUP match on two conditions?
VLOOKUP takes a single lookup value, so the usual workaround is a helper column such as =A2&B2 that joins both keys, placed as the first column of the range. If you would rather not add a helper column, INDEX/MATCH or XLOOKUP handles it far more comfortably.
Can I open the example above in Excel?
The download button gives you an xlsx built on the same table, with working IFERROR and VLOOKUP formulas in it. Change the code in the lookup cell and the results update, which makes it a convenient practice file while the syntax is still new.