# E9 | anthropic/claude-opus-5 | run 2

## Prompt

Write a spreadsheet formula that looks up the price of the product named in cell D2 from a table in columns A (names) and B (prices).

## Response

Use `XLOOKUP` if your spreadsheet supports it — it's the cleanest and safest option:

```excel
=XLOOKUP(D2, A:A, B:B, "Not found")
```

## Alternative formulas

```excel
' Classic VLOOKUP (exact match)
=VLOOKUP(D2, A:B, 2, FALSE)

' INDEX/MATCH — works in every version, immune to column insertions
=INDEX(B:B, MATCH(D2, A:A, 0))

' VLOOKUP with a clean error message
=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "Not found")
```

## Which to choose

| Formula | Best for | Notes |
| --- | --- | --- |
| `XLOOKUP` | Excel 365/2021+, Google Sheets | Built-in "if not found" argument; no column counting ([XLOOKUP docs](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)) |
| `VLOOKUP` | Older Excel, maximum compatibility | Must include `FALSE` for exact match ([VLOOKUP docs](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)) |
| `INDEX/MATCH` | Legacy files, flexible layouts | Can look left; unaffected by inserted columns |

## Practical tips

Replace whole-column references with a defined range or table (e.g. `A2:B500` or `Products[Price]`) to speed up large sheets.

The `FALSE` / `0` argument is essential — omitting it triggers an approximate match on unsorted data and returns wrong prices silently.

If your names contain trailing spaces, wrap the lookup value: `XLOOKUP(TRIM(D2), A:A, B:B, "Not found")`.

For multiple criteria (e.g. name *and* size), use `=XLOOKUP(1, (A:A=D2)*(C:C=E2), B:B, "Not found")`.

Do you need this to return several columns at once, such as price plus SKU or stock level?
