XLOOKUP vs VLOOKUP: Which to Use, and How AI Can Write Both
A simple comparison of XLOOKUP and VLOOKUP in Excel, which versions support each, the mistakes that cause wrong results, and prompts to get AI to write and explain lookups for you.
Lookups are probably the most used advanced formulas in office Excel: find a product code and return its price, find an employee ID and return their department. For years VLOOKUP was the standard. Newer Excel versions have XLOOKUP, which is simpler and safer. Here is how they compare, and how an AI assistant can write either one for you.
The short answer
- You have Excel for Microsoft 365, or Excel 2021 or later? Use XLOOKUP.
- You have Excel 2019, 2016 or older, or you share files with people who do? Use VLOOKUP or INDEX/MATCH.
If you are not sure, check File → Account in Excel. And always tell the AI your version, because XLOOKUP in an older Excel just shows #NAME?.
How they compare
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Available in | All versions | Microsoft 365, Excel 2021 and later |
| Looks left of the search column | No | Yes |
| Default match | Approximate (unless you add FALSE) | Exact |
| Breaks when columns are inserted | Yes, if you use a column number | No |
| Built-in "not found" message | No (needs IFERROR) | Yes |
The same lookup, both ways
Sheet Products has Code in column A and Price in column C. Your code is in B2.
VLOOKUP:
=VLOOKUP(B2, Products!$A:$C, 3, FALSE)
XLOOKUP:
=XLOOKUP(B2, Products!$A:$A, Products!$C:$C, "Not found")
The XLOOKUP version reads like a sentence: find B2 in column A, return column C, say "Not found" if missing.
The VLOOKUP mistake that gives wrong answers silently
If you leave out the last argument (FALSE or 0), VLOOKUP uses approximate match. On unsorted data it can return a price from the wrong row, with no error at all. Many costly spreadsheet mistakes start here.
When you ask an AI for a VLOOKUP, say "exact match", and check that the formula ends with FALSE or 0.
Prompts to get AI to write your lookup
Excel for Microsoft 365. In sheet Orders, B2 has a product code. Sheet
Products has Code in column A and Price in column C. Write an XLOOKUP for
D2 that returns the price, or "Not found" if the code is missing.
Explain each part in one line.
For files shared with older Excel:
Write the same lookup for Excel 2016 using INDEX/MATCH with exact match,
showing "Not found" when missing.
For two conditions, such as supplier and product:
Sheet Rates: A = Supplier, B = Product, C = Rate. My B2 = Supplier and
C2 = Product. Return the matching Rate or "No rate". Give one formula for
Excel 365 and one for Excel 2016.
Why lookups fail even when the value is there
If you get #N/A or "Not found" for a code you can clearly see, check these:
- Extra spaces. "P-101 " is not "P-101". Use
TRIMor clean the data. - Number vs text. 101 stored as a number in one sheet and as text in the other.
- Slightly different spelling. "Sharma Traders" vs "Sharma Traders Pvt Ltd".
- Range too short. The table grew beyond the rows in the formula.
You can paste the formula and an example of both values into the AI and ask: "Why does this lookup fail, and how do I test each possible cause?"
Check before you trust
An AI-written lookup that returns a value is not automatically right. Quick checks:
- Pick three rows and look up the values by hand.
- Count the "Not found" results and find out why each one is missing.
- Change one source value and see if the result updates.
Tip: use Excel tables
Select your product list and press Ctrl+T to make it a table. Then ask the AI to "rewrite the formula using structured references for a table named Products". Your lookups keep working as the list grows, without editing ranges.
Learn more
Lookups are lesson 5 of our free course AI for Excel: Beginner to Confident, which also covers cleaning data, pivot tables and checking AI formulas properly.