VLOOKUP and XLOOKUP explained simply
Pull a price, name or code from one sheet into another automatically, and understand why it sometimes says #N/A.
A lookup formula answers one question: "I have this reference — go and find the matching detail from that other list." It is the single most useful thing to learn in Excel.
-
Understand the four parts of VLOOKUP
The formula reads =VLOOKUP(what you are looking up, where to look, which column to bring back, FALSE). The final FALSE means "exact match only" and you should include it nearly every time.
-
Write your first one
If A2 holds a product code and your price list sits in Sheet2 columns A to D with the price in column D, then in your new cell type =VLOOKUP(A2,Sheet2!$A:$D,4,FALSE). The dollar signs lock the range so you can drag the formula down.
-
Use XLOOKUP if you have it
Microsoft 365 and Office 2021 onwards include XLOOKUP, which is far easier: =XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$D:$D,"Not found"). You point at the lookup column and the answer column separately, so it does not matter which order they sit in, and you can supply your own message when nothing matches.
-
Fix #N/A errors
The commonest causes are stray spaces (wrap the lookup value in TRIM), a number stored as text (look for the little green triangle), or a lookup column that is not the leftmost column of your VLOOKUP range. To hide genuine no-matches neatly, wrap it: =IFERROR(your formula,"").
-
Turn the results into values
Once the numbers are right, select them, press Ctrl+C then Ctrl+Alt+V and choose Values. The formulas are replaced by the answers, so the file no longer depends on the other sheet.
Common questions
Why does my lookup return the wrong row?
You almost certainly left out the final FALSE. Without it Excel does an approximate match and returns the nearest lower value.
Can it look to the left?
VLOOKUP cannot. Use XLOOKUP, or the older INDEX/MATCH combination: =INDEX(Sheet2!A:A,MATCH(A2,Sheet2!D:D,0)).