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

 To look up the price in Column B matching the product name in cell D2 from Column A, you can use the modern `XLOOKUP` function or the classic `VLOOKUP` function. Both formulas search Column A for the exact product name and return the corresponding price from Column B.

## Modern Standard: XLOOKUP

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

This formula searches for the text in cell D2 within column A and returns the matching value from column B. If the product name does not exist in Column A, it safely returns `"Not Found"` instead of an error message.

## Classic Alternative: VLOOKUP

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

This formula searches the leftmost column of range `A:B` for the exact match (`FALSE`) of cell D2 and retrieves the corresponding value from the second column.

## Comparison of Lookup Options

| Formula | Syntax | Key Advantage |
| --- | --- | --- |
| **XLOOKUP** | `=XLOOKUP(D2, A:A, B:B, "Not Found")` | Built-in error handling, cleaner syntax, works across Microsoft 365 and modern Google Sheets |
| **VLOOKUP** | `=VLOOKUP(D2, A:B, 2, FALSE)` | Fully compatible with legacy versions of Excel (Excel 2016 and older) |
| **INDEX / MATCH** | `=INDEX(B:B, MATCH(D2, A:A, 0))` | Flexible legacy alternative that avoids referencing entire columns |

Would you like assistance adding dynamic drop-down lists or handling case-sensitive product names in your setup?
