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

You can look this up with XLOOKUP in modern Excel and Google Sheets, or VLOOKUP / INDEX-MATCH if you are on an older version. All three formulas below will return the price from column B where column A matches the product name in D2.

## Recommended formula

For Excel 365, Excel 2021+, and Google Sheets:

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

**What it does:** Looks for the exact value from D2 in column A, and returns the corresponding value from column B. If not found, it returns "Not found".

A version that works with a specific table range:

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

## Alternative formulas

**1. VLOOKUP - most compatible**

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

Searches for D2 in the first column of range A:B and returns the 2nd column. `FALSE` forces an exact match.

**2. INDEX + MATCH - more robust**

```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. This works even if you later insert columns.

To hide errors if the product doesn't exist:

```excel
=IFNA(XLOOKUP(D2, A:A, B:B), "")
=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "")
```

Do you want the formula to also handle duplicate product names or return the total price for a quantity?
