Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
VLOOKUP
It finds Earbuds in the first column of F2:G5 and brings back column 2, the price. FALSE means an exact match.
Try this lesson free →Example
It does the same job as XLOOKUP. You give it the whole table and say which column to bring back:
=VLOOKUP(B2,F2:G5,2,FALSE)| Order | Product | Qty | Price | Product | Price | Look up | Price | |
|---|---|---|---|---|---|---|---|---|
| O101 | Earbuds | 2 | Earbuds | 35 | Charger | |||
| O102 | Charger | 1 | Charger | 15 | ||||
| O103 | Phone case | 3 | Phone case | 8 | ||||
| O104 | Screen guard | 4 | Screen guard | 5 | ||||
| O105 | Earbuds | 1 |
Key points
B2What to look for: this order's product.
F2:G5The whole table. VLOOKUP always searches its first column.
2Which column to bring back: the price is column 2 of F2:G5.
FALSEExact match only. Leave it out and VLOOKUP can bring back the wrong row.
Put together: =VLOOKUP(B2,F2:G5,2,FALSE). It gives the same answer as =XLOOKUP(B2,F2:F5,G2:G5). In new files, XLOOKUP is easier.
Practice
1. In D3, look up the price for order O102 with VLOOKUP.
=VLOOKUP(B3,F2:G5,2,FALSE)2. In D4, look up the price for order O103 with VLOOKUP.
=VLOOKUP(B4,F2:G5,2,FALSE)3. In D5, look up the price for order O104 with VLOOKUP.
=VLOOKUP(B5,F2:G5,2,FALSE)4. In D2, look up the price for order O101 with VLOOKUP.
=VLOOKUP(B2,F2:G5,2,FALSE)5. In I2, use VLOOKUP to look up the price of whatever product is typed in H2.
=VLOOKUP(H2,F2:G5,2,FALSE)Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →