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
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
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 like this
Write an Excel or Google Sheets Formula from a Plain-English Description
Get a ready-to-paste Excel or Google Sheets formula built for your version and columns, with a step-by-step walkthrough and one test case to check it.
Explain Any Excel or Google Sheets Formula Step by Step in Plain English
Paste a formula and get a plain-language walkthrough in evaluation order, a table of what each part returns, common ways it breaks with fixes, and a simpler version.
Fix an Excel or Google Sheets Formula That Returns an Error or Wrong Result
Get a ranked list of the likely reasons your formula fails, a quick check for each, and a corrected formula you can paste straight into your sheet.
Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel
You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.
Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts
You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.
Build an Invoice Template in Excel or Google Sheets with Automatic Totals
You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.