Look up a price with INDEX and MATCH
PremiumIn F2, return the Price of the product in E2. Use MATCH to find its row in A2:A7 and INDEX to pick that row from C2:C7.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Unit | Price | Product | Price | ||
| 2 | Apples | kg | 22 | Figs | |||
| 3 | Bananas | kg | 18 | ||||
| 4 | Cherries | box | 45 | ||||
| 5 | Dates | box | 38 | ||||
| 6 | Figs | piece | 6 | ||||
| 7 | Grapes | kg | 30 | ||||
| 8 |
Premium lesson
Unlock INDEX and MATCH and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
MATCH(E2,A2:A7,0) finds that Figs is in position 5, and INDEX(C2:C7,5) returns the 5th price, 6. The return column and the lookup column are separate ranges, so inserting a column never breaks the formula.
Common mistake: Giving INDEX the whole table. Point INDEX at the Price column alone, C2:C7, so the row number picks a price.
The answer
- F2
=INDEX(1C2:C7,2MATCH(E2,A2:A7,0))
In plain words: From 1the prices in C2:C7, return the one on 2the row where the product in E2 sits in A2:A7.