Pick XLOOKUP, VLOOKUP or INDEX MATCH and Get the Exact Formula

You get one recommended lookup method for your Excel version and data layout, a paste-ready formula, and clear handling for values that have no match.

Prompt

Prompt
You are a spreadsheet analyst who builds lookup formulas for finance and operations teams.

Help me pick the right lookup method (XLOOKUP, VLOOKUP or INDEX MATCH) for my task and give me a formula I can paste. Before answering, ask me these questions in one message and wait for my reply:
1. Which app and version do I use (Excel 365, Excel 2019 or older, Excel for Mac, Google Sheets)?
2. Where is the lookup table: sheet name, the column holding the key, the column holding the value I want, and the row range?
3. Where does my lookup value sit, and do I need an exact match or a range match (like price tiers)?
4. What should the cell show when there is no match?

Rules:
- Recommend exactly one method and say in one sentence each why the other two lose for my case.
- Use my real sheet names, columns and ranges, with absolute references so the formula can be copied down.
- Handle missing matches explicitly. Prefer IFNA over IFERROR so real errors are not hidden.
- Flag it if the key column sits to the right of the return column, has duplicates, or may contain trailing spaces or numbers stored as text.

Output format:
1. Recommendation (one line)
2. Formula (code block)
3. How it works (3-4 bullets)
4. Missing-match behavior
5. Fallback formula for a different version or app
6. Quick test: two rows to check by hand

End with one line on any assumption you made about my data.

Example output

At a glanceLookup options for Excel 2019, price left of SKUINDEX MATCH is recommended; XLOOKUP is the 365 fallback
ItemINDEX MATCHXLOOKUP
Available in Excel 2019YesNo (Excel 365 or Google Sheets)
Returns column left of keyYesYes
Missing SKU handlingWrapped in IFNABuilt-in "Not found" argument
Exact match settingMATCH type 0Match mode 0

Example for: Excel 2019 on Windows, Orders sheet needs unit price from a Products sheet where SKU is in column C and price is in column B, rows 2-500; missing SKUs should show "Not found".

1. Recommendation

Use INDEX MATCH. Your price column (B) sits left of the SKU column (C), and Excel 2019 has no XLOOKUP.

  • XLOOKUP: not available in Excel 2019.
  • VLOOKUP: can only return columns to the right of the key, so it cannot reach column B without rearranging the sheet.
2. Formula

Put this in Orders!D2 and copy down:

=IFNA(INDEX(Products!$B$2:$B$500, MATCH(A2, Products!$C$2:$C$500, 0)), "Not found")
3. How it works
  • MATCH(A2, Products!$C$2:$C$500, 0) finds the row position of the SKU in A2; the 0 forces an exact match.
  • INDEX(Products!$B$2:$B$500, ...) returns the price from that same row position.
  • $ signs lock the ranges so they do not shift when you copy the formula down.
  • A2 stays relative, so row 3 looks up A3, and so on.
4. Missing-match behavior

If the SKU is not in Products, MATCH returns #N/A and IFNA swaps it for "Not found". Other errors, such as a #REF! from a deleted column, still show, so you will notice real problems.

Watch for:

  • Duplicates: if a SKU appears twice in column C, only the first price is returned.
  • Text numbers: a SKU like 10045 stored as text will not match 10045 stored as a number. Use TRIM or Text to Columns to clean column C.
5. Fallback formula (Excel 365 or Google Sheets)
=XLOOKUP(A2, Products!$C$2:$C$500, Products!$B$2:$B$500, "Not found", 0)
6. Quick test
Orders!AExpected result
SKU-1042 (exists in Products row 17, price 24.50)24.50
SKU-9999 (not in Products)Not found

Assumption: Products has a header in row 1 and no data past row 500; widen the ranges if your list grows.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for version and column layout first means the answer avoids XLOOKUP on old Excel and VLOOKUP when the return column is to the left.
  • Requiring one recommendation with reasons the others lose stops vague comparisons and gives a decision.
  • Preferring IFNA and flagging duplicates or text-stored numbers catches the most common silent lookup errors.

Use when you need to pull values from another table and are unsure which lookup to use. Have your Excel or Sheets version and column letters ready. Works in ChatGPT, Claude and Gemini.

Common edits

  • Ask for a version that returns multiple matches using FILTER instead of a single value.
  • Request a two-condition lookup, such as matching on both SKU and region.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets