Excel: XLOOKUP and VLOOKUP explained simply
A lookup formula finds a value in a list and brings back something from the same row. For example, you type a product code and Excel fetches the price. It is just like looking up a name in a phone book and reading across to the number.
Which one should I use?
XLOOKUP is the newer, easier choice. It is available in Microsoft 365 and Excel 2021 and later. VLOOKUP is older but works in every version, so you will see it in many workbooks. If a colleague on an older Excel needs to open your file, use VLOOKUP.
XLOOKUP
The pattern is: what to find, where to find it, what to return.
Suppose codes are in A2:A50 and prices are in C2:C50, and you typed a code in E2. In F2 type:
=XLOOKUP(E2, A2:A50, C2:C50, "Not found")
Excel looks for E2 in the code column and returns the matching price. The last part is the message shown if there is no match.
VLOOKUP
The pattern is: what to find, the table, which column to bring back, and exact match.
=VLOOKUP(E2, A2:C50, 3, FALSE)
The table must start with the column holding the codes. The 3 means 'bring back the third column of that table'. FALSE means 'exact matches only', and you almost always want this.
Tips
- Press the F4 key while the cursor is on a range to lock it with dollar signs. This stops it shifting when you copy the formula down.
- Both formulas ignore capital letters.
- Excel starts suggesting the formula after you type =XLOOKUP( and shows what each part means.
Common problems
- #N/A: nothing matched. Check for spelling differences or extra spaces. Numbers stored as text will not match real numbers (see our guide on numbers stored as text).
- Wrong answer from VLOOKUP: you may have left off FALSE.
- #NAME?: you may be using XLOOKUP in an older version of Excel that does not have it.
The formulas are identical on a Mac.
When to get more help
If a lookup returns wrong values in something important, such as pricing or payroll, ask us to check it before it is used.