Look up a price with VLOOKUP and XLOOKUP
Bring each order's price over from a price list with VLOOKUP or XLOOKUP, and avoid the missing-FALSE trap.
Do this lesson on your computer.
It runs beside a practice Google Sheets in your browser, and every step is checked as you go. Free, with no sign-up.
On your computer, go tolearnford.com/go/TTD6
In this lesson
- Always end VLOOKUP with FALSE.
- Lock the ranges with $ before you copy a lookup down.
#N/Ameans "not found": the value is missing from the list, misspelled, or has an extra space.
The steps
11 steps: 5 explanations, 4 Do this, 2 questions, then Your turn.
Cheat sheet
=VLOOKUP(what to find, range, column number, FALSE). What you look for must be in the first column of the range; the column number counts from that column. Google Sheets calls the arguments search_key, range, index and is_sorted.- Always end VLOOKUP with FALSE: "is_sorted is FALSE" means exact match. Left out, Sheets assumes the list is sorted and returns wrong values on an unsorted list without any error.
=XLOOKUP(what to find, where to look, what to return): exact match by default, no column to count, and the result column can sit left of the codes. Prefer it unless someone will open the file in Excel 2019 or older, which only has VLOOKUP.- Lock the ranges with $ before you copy a lookup down. F4 (Windows) or Fn+F4 (Mac) adds the $ signs to the reference under the cursor.
#N/Ameans "not found": the value is missing from the list, misspelled, or has an extra space.