# How do I use VLOOKUP or XLOOKUP for a price list?

To look up a price from a list, keep items in one column and prices in the next, then use =XLOOKUP("Deep clean", A2:A50, B2:B50) or =VLOOKUP("Deep clean", A2:B50, 2, FALSE) in Google Sheets. VLOOKUP needs the item in the first column of its range, and FALSE forces an exact match; both return #N/A when the item is missing. ProfitDrawer's Expenses tab uses the related INDEX and MATCH pair to fill in each category's Business? setting.

**Price list layout:** A = service or item, B = price, rows 2 to 50.

**XLOOKUP:** `XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)`

- lookup_range must be a single row or column; result_range should be the same size.
- missing_value sets what shows when nothing matches, for example `=XLOOKUP(E2, $A$2:$A$50, $B$2:$B$50, "Not listed")`. It is #N/A by default.
- match_mode 0 (exact) is the default.

**VLOOKUP:** `VLOOKUP(search_key, range, index, is_sorted)`

- The item must be in the first column of range.
- index is the column number to return, counting from 1, so 2 = price.
- If you leave out is_sorted, Sheets uses approximate match. Google recommends `FALSE` for consistent results.

Google gives `XLOOKUP` as the replacement for `VLOOKUP`; for example `XLOOKUP("Mower", A2:A, E2:E)` gives the same result as `VLOOKUP("Mower", A2:E, 5, FALSE)`.

**Tips:**

- Use `$` signs (press F4 while editing a formula) so the list range stays fixed when you copy the formula down.
- Spell items the same way in the list and the lookup cell, or you get #N/A.
- Wrap the formula in IFERROR to show a blank instead of an error.

- Source: [XLOOKUP function: Google Docs Editors Help](https://support.google.com/docs/answer/12405947)
- Updated: 2026-10-29
- Published: 2026-10-29
- Category: Google Sheets and Excel how-tos
- Canonical: https://profitdrawer.com/answers/how-do-i-use-vlookup-or-xlookup-for-a-price-list.html
- Publisher: ProfitDrawer (https://profitdrawer.com)