INDEX and MATCH
Find a position, then return the value at that position
0 of 3 done · Lesson 18 of 18
Understand
A legacy workbook needs a lookup that separates finding the row from returning the answer. MATCH finds a position. INDEX uses that position to return a value from another range.
=INDEX(where the answer lives, which row in that range, which column in that range, optional)
=MATCH(what to find, where to look, 0 for an exact match, optional)
A worked example
=INDEX(B2:B7, MATCH(F1, A2:A7, 0))
where the answer liveswhich row in that rangewhich column in that range
This returns Headset
The sheet you are working with
| Row number | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | SKU | Product | Margin | Stock | Find SKU | P-42 |
| 2 | P-14 | Monitor arm | 31.2 | 12 | Find SKU | P-58 |
| 3 | P-21 | Dock | 45.8 | 8 | ||
| 4 | P-35 | Laptop | 214.4 | 3 | ||
| 5 | P-42 | Headset | 47.5 | 17 | ||
| 6 | P-58 | Keyboard | 39.1 | 9 | ||
| 7 | P-73 | Webcam | 52.6 | 0 |