Build the right Excel lookup formula with XLOOKUP or INDEX/MATCH
Gets you a working lookup that pulls values from another table, handles missing matches and multiple criteria, and fits your Excel version.
The prompt
Build a lookup formula to pull values from one table into another.
Table I'm filling in: [sheet name, columns, and 5-10 sample rows]
Table I'm looking up from: [sheet name, columns, and 5-10 sample rows]
What I want returned: [e.g. "the manager's name for each employee ID", "the price that applied on the order date"]
Match on: [one column, or several - e.g. "product AND region"]
Exact or range match: [exact / closest lower value, like tax bands or rate tiers]
If no match is found, show: [e.g. blank, 0, "Not found"]
My tool: [Excel 365 / Excel 2016-2019 / Google Sheets]
Return:
1. The formula for the first row, ready to fill down, using the best function for my version: XLOOKUP in Excel 365; INDEX/MATCH in older Excel (avoid VLOOKUP with a hard-coded column number unless I ask); multiple criteria with XLOOKUP on concatenated keys or a FILTER, or INDEX/MATCH with an array condition.
2. Why it works, in 3-4 lines.
3. Checks for common failures and how to fix each:
- Numbers stored as text in one table and numbers in the other.
- Trailing spaces or different capitalization.
- Duplicate keys in the lookup table (which match gets returned).
- Unsorted data for approximate matches.
- Ranges that shift when copied (missing $ signs) or don't cover new rows (use a Table or whole columns).
4. A test: 3 sample rows with the expected results, including one with no match.
5. Return multiple columns at once if that saves me formulas (XLOOKUP returning an array).
If my sample data shows a problem like duplicate keys, point it out before giving the formula.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- sheet name, columns, and 5-10 sample rows
- one column, or several - e.g. "product AND region"
- exact / closest lower value, like tax bands or rate tiers
- e.g. blank, 0, "Not found"
- Excel 365 / Excel 2016-2019 / Google Sheets
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Explain a dataset or report in plain business languageExplains what a dataset or report contains, what each column or metric means, and what it shows, in words anyone can follow.
- Find what my dashboard is not telling meReviews a dashboard for blind spots, missing comparisons, vanity metrics, and misleading views, and suggests what to add.
- Flag impossible or suspicious values in my dataChecks your data against logic and business rules for impossible, placeholder, and implausible values, and gives reusable validation rules.
- Create a data-quality checklist for a recurring report or datasetA reusable pre-publish checklist with checks, formulas, pass thresholds, owners, and what to do when a check fails.