# E9 | google/gemini-3.6-flash | 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

To look up the price of a product named in cell D2 from a table in columns A and B, the primary formula to use is `XLOOKUP` [Microsoft Excel XLOOKUP Guide](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929). Classic alternatives like `VLOOKUP` or `INDEX`/`MATCH` can also perform this exact lookup across Microsoft Excel and Google Sheets [Google Sheets Function List](https://support.google.com/docs/table/25219).

## Recommended Formula: XLOOKUP

The `XLOOKUP` function is the standard and most reliable method for modern spreadsheet applications.

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

**Formula Breakdown**
- **D2**: The lookup value containing the product name.
- **A:A**: The lookup range containing the list of product names.
- **B:B**: The return range containing the prices.
- **"Not Found"**: An optional value returned if the product name in D2 does not exist in column A.

## Alternative Formulas

**VLOOKUP Method**
Use `VLOOKUP` for backward compatibility with older spreadsheet versions.

```excel
=VLOOKUP(D2, A:B, 2, FALSE)
```
The number `2` directs the formula to pull from the second column in the range (Column B), and `FALSE` ensures an exact match.

**INDEX and MATCH Method**
The `INDEX` and `MATCH` combination is a versatile alternative that works across all spreadsheet software.

```excel
=INDEX(B:B, MATCH(D2, A:A, 0))
```
`MATCH` finds the row number where the product name appears in column A, and `INDEX` retrieves the price from that exact row in column B.

Would you like help expanding this formula to handle duplicate product names or calculate total costs based on quantity?
