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
FALSEfor 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 · Updated
Tools and spreadsheets for this
Related questions
All questions on ProfitDrawer Answers →
General information, not tax, legal or financial advice. Check the rules for your state and your situation.