# 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.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Advanced
- Tags: Data & Analytics, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/build-the-right-excel-lookup-formula-with-xlookup-or-index-match

## Prompt

```text
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.
```

## 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

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.