Part of the free Module 13: Excel Formulas with AI · Lesson 6 of 9 · Full Excel course
Updated on 5 September 2026 · XLOOKUP and FILTER need Excel 365 or 2021; INDEX MATCH and VLOOKUP work in every version, including 2019 and 2016. Any AI chat tool or Copilot can write the formulas.
To get a correct lookup formula from AI, tell it five things: the value you are looking up, the column that contains it, the column you want back, whether the match must be exact, and what to show when nothing is found. Add your Excel version and AI will choose between XLOOKUP, INDEX MATCH and VLOOKUP correctly. Leave any of those out and it guesses.
Which lookup function to ask AI for
AI defaults to XLOOKUP because it is the modern answer. That is right for Excel 365 and 2021, and wrong for anyone on Excel 2019 or 2016, or sharing a file with them. Decide first, then tell the model.
| Question | VLOOKUP | INDEX MATCH | XLOOKUP |
|---|---|---|---|
| Excel version | All versions | All versions | 365 and 2021 or later |
| Return a column to the left of the lookup column? | No | Yes | Yes |
| Default match mode | Approximate unless you add FALSE | Approximate unless you add 0 | Exact |
| Built-in not-found text | No, wrap in IFERROR or IFNA | No, wrap in IFERROR or IFNA | Yes, the fourth argument |
| Breaks when columns are inserted? | Yes, the column number shifts | No | No |
| Ask AI for it when | You must match an old template | The file is shared with Excel 2019 or older users | Everyone is on Excel 365 or 2021 |
Sample data used in this lesson
A Products sheet holds a price list in A1:C8. The Orders sheet has SKUs typed in column E and needs the price in column F.
| SKU (A) | Product (B) | Price (C) |
|---|---|---|
| P-101 | Desk lamp | 25 |
| P-102 | Office chair | 120 |
| P-103 | Monitor arm | 45 |
| P-104 | Keyboard | 30 |
| P-105 | Mouse | 15 |
| P-106 | Webcam | 60 |
| P-107 | Headset | 55 |
Prompt 1: an exact-match XLOOKUP with not-found text
Goal: return the Price for the SKU typed in E2.
Data: sheet Products, A1:C8, headers SKU, Product, Price; data in rows 2 to 8. The lookup SKU is in E2 on the same sheet.
Rules: exact match; show Not found if the SKU does not exist; the formula will be copied down column F.
Excel 365, English (UK), comma separators.
Give the formula only for F2, then one sentence explaining it.=XLOOKUP(E2,$A$2:$A$8,$C$2:$C$8,"Not found")If the price list is an Excel Table named Products, ask for structured references and expect =XLOOKUP(E2,Products[SKU],Products[Price],"Not found"), which needs no dollar signs. Results: E2 = P-104 gives 30, P-102 gives 120, P-109 gives Not found.
Prompt 2: the same lookup for Excel 2019
Same data as above. I use Excel 2019, so do not use XLOOKUP. Return Not found when the SKU is missing. Formula only for F2.=IFERROR(INDEX($C$2:$C$8,MATCH(E2,$A$2:$A$8,0)),"Not found")The 0 in MATCH forces an exact match; without it MATCH assumes the list is sorted and returns the wrong row silently. A model may also offer =IFERROR(VLOOKUP(E2,$A$2:$C$8,3,FALSE),"Not found"), which is fine here. It stops being fine when you need a column to the left of the lookup column: to find the SKU for a product name you need =INDEX($A$2:$A$8,MATCH("Mouse",$B$2:$B$8,0)), which returns P-105, and VLOOKUP cannot do that at all.
Prompt 3: a two-way lookup by row and column
Prices vary by region, so the sheet is a matrix: product names in A2:A5, region codes in B1:D1, prices in B2:D5. G2 holds a product and H2 a region.
| Product | UK | US | IN |
|---|---|---|---|
| Desk lamp | 25 | 30 | 2000 |
| Office chair | 120 | 140 | 9500 |
| Monitor arm | 45 | 50 | 3500 |
| Keyboard | 30 | 35 | 2400 |
Goal: return the price at the intersection of the product in G2 (rows) and the region in H2 (columns).
Data: A1:D5; product names in A2:A5, region codes in B1:D1, prices in B2:D5.
Rules: exact match on both; show Not found if either is missing.
Excel 365. Formula only for I2, then one sentence.=XLOOKUP(G2,$A$2:$A$5,XLOOKUP(H2,$B$1:$D$1,$B$2:$D$5,"Not found"),"Not found")The inner XLOOKUP returns the whole column for the region; the outer one picks the row. With G2 = Office chair and H2 = US the result is 140. The Excel 2019 version is =INDEX($B$2:$D$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$D$1,0)).
Prompt 4: a lookup on two criteria
When the price list is a flat table with Product in A, Region in B and Price in C (rows 2 to 6), a single lookup column is not enough.
| Product | Region | Price |
|---|---|---|
| Desk lamp | UK | 25 |
| Desk lamp | US | 30 |
| Office chair | UK | 120 |
| Office chair | US | 140 |
| Keyboard | UK | 30 |
Goal: return the Price where Product equals E2 and Region equals F2.
Data: A1:C6, headers Product, Region, Price; data in rows 2 to 6.
Rules: both conditions must match exactly; each product and region pair appears once; show Not found otherwise.
Excel 365. Formula only for G2, then one sentence.=XLOOKUP(1,($A$2:$A$6=E2)*($B$2:$B$6=F2),$C$2:$C$6,"Not found")Multiplying the two comparisons gives 1 only where both are TRUE, and XLOOKUP finds the first 1. With E2 = Office chair and F2 = US the result is 140. Two alternatives a good model will mention: =FILTER($C$2:$C$6,($A$2:$A$6=E2)*($B$2:$B$6=F2),"Not found"), which returns every match rather than the first, and for Excel 2019 =INDEX($C$2:$C$6,MATCH(1,($A$2:$A$6=E2)*($B$2:$B$6=F2),0)) entered with Ctrl+Shift+Enter. When the returned value is a number and each pair is unique, =SUMIFS($C$2:$C$6,$A$2:$A$6,E2,$B$2:$B$6,F2) works in every version and needs no array entry.
Prompt 5: an approximate match for bands and tiers
Commission tiers are the classic case: the threshold in A, the rate in B, sorted ascending.
| Sales from | Rate |
|---|---|
| 0 | 2% |
| 10000 | 4% |
| 25000 | 6% |
| 50000 | 8% |
Goal: return the commission Rate for the sales figure in D2, where each row's threshold is the lowest sales value that earns that rate.
Data: A1:B5, headers Sales from, Rate; data in rows 2 to 5, sorted ascending.
Excel 365. Formula only for E2, then one sentence.=XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5,,-1)The match mode -1 means “exact match or the next smaller value”. For D2 = 30000 the result is 6%. The older equivalent is =VLOOKUP(D2,$A$2:$B$5,2,TRUE), which gives the same answer but only if the thresholds are sorted ascending; XLOOKUP with -1 does not depend on sort order.
What AI gets wrong on lookups
- Approximate match by accident. VLOOKUP without FALSE and MATCH without 0 return plausible wrong values on unsorted data. Always say “exact match”.
- Relative ranges that slide. A formula that works in F2 fails in F3 because the lookup range moved. Ask for absolute references or a Table.
- XLOOKUP for the wrong version. Unless told otherwise, models assume Excel 365.
- Mismatched array sizes. A lookup array of 7 rows and a return array of 8 rows gives #VALUE!. Give exact row numbers.
- Invented arguments. Models sometimes add a case-sensitivity flag to XLOOKUP. It does not exist; use EXACT with MATCH if you need case sensitivity.
- Text versus numbers. A SKU stored as text will not match the same SKU stored as a number. Mention it if your data mixes the two.
Worked example: price every order line and total the invoice
The Orders sheet lists SKUs in E2:E6 and quantities in G2:G6. You want unit price in F, line total in H and a grand total in H8, using the Products sheet from the start of this lesson.
| SKU (E) | Price (F) | Qty (G) | Line total (H) |
|---|---|---|---|
| P-104 | 30 | 2 | 60 |
| P-101 | 25 | 1 | 25 |
| P-109 | Not found | 3 | 0 |
| P-106 | 60 | 1 | 60 |
| P-102 | 120 | 2 | 240 |
- Ask AI for the price lookup with Prompt 1, naming F2 as the target cell, and paste the returned
=XLOOKUP(E2,Products!$A$2:$A$8,Products!$C$2:$C$8,"Not found")into F2. Copy down to F6. - Ask a follow-up: “In H2, multiply Price by Qty but return 0 when Price is Not found.” Expect
=IF(ISNUMBER(F2),F2*G2,0). Copy down to H6. - In H8 enter
=SUM(H2:H6). Result: 385 (60 + 25 + 0 + 60 + 240). - Check: three of the five SKUs are in the sample list at 30, 25 and 60, and P-102 is 120. Multiply by hand and confirm 385.
- Fix the data, not the formula: P-109 is a typo for P-107. Correct it and H8 becomes 385 + 165 = 550.
Tips and common mistakes
- Give exact ranges with row numbers. “A2:C8” produces a better formula than “the price list”.
- Say where the lookup value lives. The model needs to know it is E2 on the Orders sheet, not on Products.
- Prefer Tables for shared files. Structured references grow with the data and need no dollar signs.
- Ask for the not-found text explicitly. Otherwise you get #N/A in reports and SUM still works but IF logic breaks.
- Test a value that is missing. The not-found branch is the part nobody checks until a customer sees it.
- Do not let AI pick approximate match for you. If you need bands, say so; if you need exact, say so.
- Keep a 2019 fallback in the same chat. “Now give me the same for Excel 2019” costs one line and saves a support call.
Errors and how to fix them
| Error | Cause | Fix |
|---|---|---|
| #N/A | Value not found, extra spaces, or text versus number mismatch | Check with TRIM and VALUE; add a not-found argument or IFNA |
| #NAME? | XLOOKUP or FILTER in Excel 2019 or older | Ask AI for the INDEX MATCH version |
| #VALUE! | Lookup array and return array are different sizes, or a 2019 array formula was entered without Ctrl+Shift+Enter | Match the row numbers exactly; enter the array formula correctly |
| #SPILL! | FILTER or a spilled XLOOKUP has no empty cells below | Clear the cells or move the formula |
| #REF! | VLOOKUP column number is larger than the table width, or a column was deleted | Switch to INDEX MATCH or XLOOKUP |
| Wrong value, no error | Approximate match on unsorted data | Add FALSE to VLOOKUP or 0 to MATCH; XLOOKUP is exact by default |
Practice exercise
- Type the Products table into a sheet and ask AI for a formula that returns the Product name, not the price, for a SKU in E2.
- Ask for the same formula for Excel 2016. Confirm it uses INDEX MATCH or VLOOKUP and returns the same values.
- Build the region matrix and ask for a two-way lookup. Test it with a region code that does not exist.
- Using the flat Product and Region table, ask for a formula that returns Not found when the pair is missing. Try Keyboard and US.
- Ask AI to add a case-sensitive option to your XLOOKUP. Check whether it invents an argument or uses EXACT correctly.
Key takeaways
- State the lookup value, lookup column, return column, match type, not-found text and Excel version in every lookup prompt.
- XLOOKUP is exact by default and has built-in not-found text; VLOOKUP and MATCH are approximate unless you say otherwise.
- INDEX MATCH is the safe answer for shared files and Excel 2019; XLOOKUP for Excel 365 and 2021.
- Multi-criteria lookups multiply comparisons; FILTER returns all matches, XLOOKUP the first.
- Always test a missing value and a copied-down row before trusting the formula.
Related lessons
- Excel Formulas with AI: course hub
- How to Write a Prompt for an Excel Formula: The 5-Part Template
- IF, IFS, SWITCH, AND and OR Formulas Written with AI
- Verifying AI Formulas: Test Cases, Edge Cases and Hallucinations
- XLOOKUP, XMATCH, FILTER, VLOOKUP, INDEX, MATCH and IFERROR in the formulas library
- INDEX MATCH vs VLOOKUP vs XLOOKUP: a 2026 guide and XLOOKUP with multiple criteria (blog posts)
- Microsoft Support: XLOOKUP function
Frequently asked questions
Can ChatGPT write an XLOOKUP formula?
Yes. Give it the lookup cell, the lookup range, the return range, the match type and the not-found text, plus your Excel version, and it returns a working XLOOKUP. Check that the two ranges have the same number of rows and that it did not add arguments XLOOKUP does not have.
Should I ask AI for XLOOKUP or INDEX MATCH?
Ask for XLOOKUP if everyone who opens the file uses Excel 365 or 2021. Ask for INDEX MATCH if the file is shared with Excel 2019 or older, or if you want a formula that behaves the same everywhere. Both handle left lookups and survive inserted columns; VLOOKUP does neither.
How do I do a lookup with two criteria in Excel?
In Excel 365 use XLOOKUP with the two comparisons multiplied together as the lookup array, or FILTER to return every match. In older versions use INDEX with MATCH(1, (range1=x)*(range2=y), 0) entered as an array formula, or SUMIFS when the returned value is a unique number.
Why does my VLOOKUP return the wrong value with no error?
Because the fourth argument was omitted, so VLOOKUP used approximate match and returned the nearest value from an unsorted list. Add FALSE as the fourth argument for an exact match, or switch to XLOOKUP, which is exact by default.
Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Excel 365 and in Excel 2021 and later perpetual versions. In Excel 2019 and 2016 it returns #NAME?. Ask AI for an INDEX MATCH version wrapped in IFERROR for the same result.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.