Lesson 9 of 29, Lookups
VLOOKUP
Find a row by its key and pull back a value from the same row.
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
=VLOOKUP(lookup_value, table, column_number, FALSE) searches the first column of the table for the value and returns the value from column number N of the same row.
Always pass FALSE for an exact match. The default, TRUE, assumes the first column is sorted and can quietly return the wrong row. Lock the table with $ so it stays put when you fill down.
Syntax
=VLOOKUP(F2,$A$2:$D$9,2,FALSE)Product name (column 2)=VLOOKUP(F2,$A$2:$D$9,3,FALSE)Price (column 3)Watch out for
- VLOOKUP only looks right of the first column.
- A hard-coded column number breaks when someone inserts a column. XLOOKUP, next, fixes both.
The exercises
- Look up product names. In G2, look up the product name for the SKU in F2. Fill down to G4.
- Look up prices. In H2, look up the price for the SKU in F2. Fill down to H4.
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Look up product names
- Look up prices