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 Excel in your browser, and every step is checked as you go. Free, with no sign-up.
On your computer, go tolearnford.com/go/SPJV
In this lesson
- Always end VLOOKUP with FALSE (exact match).
- 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. Excel calls the arguments lookup_value, table_array, col_index_num and range_lookup.- Always end VLOOKUP with FALSE (exact match). Left out, it means approximate match, which 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 opens 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. In Excel for Mac, ⌘T also works.
#N/Ameans "not found": the value is missing from the list, misspelled, or has an extra space.