Level 3 · List prices with VLOOKUP and IFERROR

Order lines store a product_id, not a price or a category: those live once, in the catalog. To see them next to each line you look them up: find the line’s id in the catalog and bring back a value from the same row. VLOOKUP is the oldest way to do it, and the one you will meet most in shared workbooks. In this sheet the lines are in A1:D33 and the Decor and Textiles catalog sits on the right in F1:I36.

The four arguments

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Argument	In C2	Means
lookup_value	B2	the id to find (190)
table_array	$F$2:$I$36	the whole catalog; the ids must be in its first column
col_index_num	4	which column of the table to return, counting from F as 1
range_lookup	FALSE	exact match only

For row 2, VLOOKUP finds 190 in column F and returns the 4th column of that row: 43.99. Change the 4 to a 3 and the same formula returns the category, Decor. The column number counts inside the table, not across the sheet: F is 1, G is 2, H is 3, I is 4. Ask for column 5 and VLOOKUP shows #REF!, because this table has only four.

Always write FALSE

The last argument is optional, and its default is TRUE: an approximate match. VLOOKUP then assumes the ids are sorted and settles for the largest id that isn’t above the one you asked for. On this catalog, sorted by id, that sounds harmless until a key is missing. Product 183 was dropped, and =VLOOKUP(183,$F$2:$I$36,4) returns 17.99, the price of product 182, the Nantucket Brass Wall Mirror. No error, just a wrong price. With FALSE, the same formula shows #N/A, which is the truth: 183 isn’t in the catalog.

IFERROR for ids that aren’t there

Four lines in this sheet are for dropped products: 183 (row 11), 163 (row 27) and 191 (rows 30 and 33). Each shows #N/A. IFERROR replaces an error with a value you choose:

=IFERROR(VLOOKUP(B2,$F$2:$I$36,4,FALSE),"not in catalog")

If the lookup works, IFERROR passes its result through; if it fails, the cell says “not in catalog”. Filled down, 19 lines come back as Decor, 9 as Textiles and 4 as not in catalog: 32, one per line.

IFNA does the same but only for #N/A. That difference matters: with a wrong column number, IFERROR(VLOOKUP(B2,$F$2:$I$36,5,FALSE),…) hides the #REF! and calls a real product “not in catalog”, while IFNA lets the #REF! show so you can fix the formula. Both behave the same in Google Sheets. Text is also safer than 0 as the fallback in a price column: a 0 gets added into totals, text doesn’t.

Common mistakes

• No FALSE. Missing ids silently borrow the price of their neighbour.
• Counting columns from A. The price is column 4 of F2:I36, not column 9 of the sheet.
• Unlocked table. F2:I36 filled down becomes F3:I37, and the first products drop out of the search one row at a time. Lock it: $F$2:$I$36.
• A table that starts at the wrong column. VLOOKUP only searches the first column of the table you give it. $G$2:$I$36 searches names, not ids.