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

## 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, and `VLOOKUP` as the universally compatible fallback. Both assume product names are in column A and prices in column B.

## Recommended: XLOOKUP

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

`XLOOKUP` searches the lookup array for `D2`, returns the matching value from the return array, and defaults to exact matching — no column-index counting required ([XLOOKUP function](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)). Available in Excel 365, Excel 2021+, and Google Sheets.

## Classic: VLOOKUP

```excel
=VLOOKUP(D2, $A$2:$B$1000, 2, FALSE)
```

The `FALSE` (or `0`) fourth argument forces an exact match; omitting it triggers approximate matching against unsorted data and returns wrong prices ([VLOOKUP function](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)). Wrap it to suppress errors:

```excel
=IFERROR(VLOOKUP(D2, $A$2:$B$1000, 2, FALSE), "Not found")
```

## Alternative: INDEX + MATCH

```excel
=INDEX($B$2:$B$1000, MATCH(D2, $A$2:$A$1000, 0))
```

This works in every Excel version and survives column insertions or deletions, unlike `VLOOKUP`'s hard-coded column number ([INDEX function](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)).

## Comparison

| Formula | Version support | Breaks if columns move | Handles missing values |
| --- | --- | --- | --- |
| XLOOKUP | Excel 365/2021+, Sheets | No | Built-in 4th argument |
| VLOOKUP | All versions | Yes | Needs IFERROR |
| INDEX + MATCH | All versions | No | Needs IFERROR |

## Practical notes

Use `$` absolute references on the table ranges so the formula can be filled down without shifting the lookup area ([Google Sheets VLOOKUP](https://support.google.com/docs/answer/3093318)).

If you converted the data to an Excel Table named e.g. `Products`, replace ranges with structured references: `=XLOOKUP(D2, Products[Name], Products[Price], "Not found")`.

Trailing spaces and inconsistent text cause false `#N/A` errors — wrap the lookup value in `TRIM()` if the source data is messy: `=XLOOKUP(TRIM(D2), ...)`.

Would you like a version that returns multiple columns (such as price plus SKU) or one that sums prices for duplicate product names?
