Course / Lookups

VLOOKUP: exact matches

Turn a product code into its name, price and stock level, straight from a table. Intermediate · 4 min · VLOOKUP, Exact match, col_index_num

VLOOKUP searches down the first column of a table for a value, then returns something from the same row. It turns a code like GC-147 into a product name, a price or a stock level, so you never have to hunt for it by eye.

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

• lookup_value: what to search for, usually a cell such as G2
• table_array: the whole table, here A2:D9. VLOOKUP always searches its first column
• col_index_num: which column of the table to return, counting from 1 at the table's left edge
• range_lookup: FALSE for an exact match, which is what you want for codes, names and IDs

Steps

1. The SKU to look up is in G2. In G3, return its product name, which is column 2 of the table: =VLOOKUP(G2, A2:D9, 2, FALSE)

2. In G4, look up the price. It is the same formula with a different column number, because Price is column 3 of the table.

3. Finish the card: in G5, return how many are in stock.

4. Now the payoff. Type GC-122 into G2 and watch all three answers update at once.

Remember

• =VLOOKUP(value, table, column, FALSE) finds an exact match in the table's first column.
• The column number counts from 1 at the table's left edge.
• #N/A means the value is not in the first column. Check for typos and stray spaces.
• Point lookup_value at a cell, so changing one input updates every result.

Previous: SUMPRODUCT · Next: Approximate match for tiers and bands