Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
INDEX and MATCH
MATCH finds Earbuds 1st in G2:G5, so INDEX brings back the 1st price in F2:F5: 35.
Try this lesson free →Example
VLOOKUP can't look left. MATCH finds which row the product is in, and INDEX brings back the price from that row:
=INDEX(F2:F5,MATCH(B2,G2:G5,0))| Order | Product | Qty | Price | Price | Product | Look up | Position | Price | |
|---|---|---|---|---|---|---|---|---|---|
| O101 | Earbuds | 2 | 35 | Earbuds | Charger | ||||
| O102 | Charger | 1 | 15 | Charger | |||||
| O103 | Phone case | 3 | 8 | Phone case | |||||
| O104 | Screen guard | 4 | 5 | Screen guard | |||||
| O105 | Earbuds | 1 |
Key points
MATCH(B2,G2:G5,0)Where is it? Earbuds is 1st in the Product column. The 0 means an exact match.
INDEX(F2:F5,1)Bring back the 1st cell of the Price column: 35.
F2:F5The column you bring back can be left or right of the one you search.
$F$2:$F$5Filling down? Lock both lists with $, as in Absolute references.
Put together: =INDEX(F2:F5,MATCH(B2,G2:G5,0)). MATCH finds the row, INDEX brings back the cell. It works in every version of Excel, and it can look left.
Practice
1. In I2, find the position of the product in H2 in the Product column (G2:G5), as an exact match.
=MATCH(H2,G2:G5,0)2. In J2, bring back the 2nd price in the Price column (F2:F5).
=INDEX(F2:F5,2)3. In D3, look up the price for order O102. MATCH goes inside INDEX, in place of the position.
=INDEX(F2:F5,MATCH(B3,G2:G5,0))4. In D2, look up the price for order O101 with INDEX and MATCH.
=INDEX(F2:F5,MATCH(B2,G2:G5,0))5. In J2, look up the price of whatever product is typed in H2.
=INDEX(F2:F5,MATCH(H2,G2:G5,0))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 →