Lookups Made Easy: XLOOKUP and INDEX/MATCH With AI

Lookups answer the most common question in office Excel: "find this value in that table and bring back the matching detail". Rate of a product, GST rate of an item, email of a customer, manager of an employee. AI makes lookups easy, as long as you tell it which version of Excel you have.

Which lookup to use

Always state your version in the prompt. XLOOKUP in an older Excel shows #NAME?.

A basic lookup

Sheet Orders has a product code in column B. Sheet Products has Code in column A and Price in column C.

Excel for Microsoft 365. In sheet Orders, B2 has a product code. Sheet
Products has Code in column A and Price in column C (rows 2 to 300).
Write a formula for D2 that returns the Price for the code in B2, and
shows "Not found" if the code is missing. Explain each part.

Typical answer:

=XLOOKUP(B2, Products!$A$2:$A$300, Products!$C$2:$C$300, "Not found")

The four parts: what to find, where to look, what to return, and what to show if there is no match.

The same lookup for older Excel:

=IFERROR(INDEX(Products!$C$2:$C$300, MATCH(B2, Products!$A$2:$A$300, 0)), "Not found")

The 0 in MATCH means exact match. Leaving it out is a classic mistake that returns wrong prices without any error.

Lookup with two conditions

Rates often depend on two things, such as supplier and product:

Excel for Microsoft 365. Sheet Rates: A = Supplier, B = Product, C = Rate.
In my sheet, B2 = Supplier and C2 = Product. Return the matching Rate,
or "No rate" if none. Give one formula for Excel 365 and one for Excel 2016.

Ask for both versions if your file will be opened by colleagues on different Excel versions.

Why lookups fail

When you see #N/A or "Not found" for values that clearly exist, the cause is almost always one of these:

• Extra spaces: "P-101 " is not "P-101". Fix with TRIM (lesson 4).
• Number vs text: the code 101 stored as a number in one sheet and as text in the other.
• Different spelling: "Sharma Traders" vs "Sharma Traders Pvt Ltd".
• Wrong range: the table grew beyond the rows in the formula.

My XLOOKUP returns "Not found" for code 101, but I can see 101 in the
Products sheet. What should I check first, and how do I test each cause?

Tip: turn both tables into Excel tables with Ctrl+T. Formulas then use table names instead of fixed rows, so they keep working when you add data. Ask the AI to "rewrite the formula using structured references for a table named Products".

Missing values are information

Do not hide every lookup error with IFERROR. A "Not found" can mean a new product nobody added to the rate list. Instead:

• Show a clear message like "Not found" or "Check rate".
• Use Conditional Formatting to highlight those cells in red.
• Count them: ask the AI for a formula that counts how many orders have no rate.

Practice task

Create two small sheets: a list of 10 orders with product codes, and a product list with codes and prices. Make two of the order codes have an extra space, and one code missing from the product list. Ask the AI for a lookup formula, then use it to find and fix all three problems.

Key takeaways

• Use XLOOKUP in Excel 365 or 2021+, INDEX/MATCH in older versions; always state your version.
• Give the lookup value cell, the search column, the return column and the not-found text.
• Most failures come from spaces, number-vs-text, spelling or ranges.
• Highlight and count "Not found" results instead of hiding them.