# E9 | meta/muse-spark-1.1 | 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

The easiest way to do this is with XLOOKUP or VLOOKUP. Both will take the value in D2, find it in column A, and return the matching price from column B with an exact match.

## Recommended formula (Excel + Google Sheets)

Use XLOOKUP — it's simpler and handles not-found cases:

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

If you want your table limited to a specific range, like A2:B100:

```excel
=XLOOKUP(D2, A2:A100, B2:B100, "Not found")
```

This returns the price from column B where column A equals D2. Docs: [XLOOKUP Function Guide](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84d9-9f8d54be11a3).

## Alternative formulas

**1. VLOOKUP (classic Excel / Sheets):**
```excel
=VLOOKUP(D2, A:B, 2, FALSE)
```
Looks up D2 in the first column of A:B and returns column 2. `FALSE` forces an exact match. [VLOOKUP Documentation](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1).

**2. INDEX + MATCH (works in all versions, more flexible):**
```excel
=INDEX(B:B, MATCH(D2, A:A, 0))
```
`MATCH` finds the row number of D2 in column A, `INDEX` returns that row from column B. [MATCH Documentation](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a).

## How to avoid common errors

- **Use absolute ranges if copying down:** `=XLOOKUP(D2, $A$2:$A$100, $B$2:$B$100)`
- **Return 0 instead of error:** `=IFNA(XLOOKUP(D2, A:A, B:B), 0)` or `=IFNA(VLOOKUP(D2, A:B, 2, FALSE), 0)`
- **Trim extra spaces:** If D2 or column A has spaces, use `=XLOOKUP(TRIM(D2), A:A, B:B)`

Do you want the formula to sum all matching prices if the product name appears multiple times in column A?
