INDEX with MATCH

Part of the Formulas and Data Analysis section of Coddy's Excel journey. Lesson 19 of 28.

Nest an exact MATCH inside INDEX to retrieve a value by key. The lookup and return ranges must line up. This can also return values to the left of the searched column, unlike a basic VLOOKUP.

E2 contains P2, A2 contains P3, A3 contains P1, A4 contains P2, B2 contains Pad, B3 contains Pen, B4 contains Clip.

Example: =INDEX(B2:B4,MATCH(E2,A2:A4,0)).

MATCH returns position three, then INDEX retrieves the third name.

Align lookup and return ranges, then pass the exact MATCH position to INDEX.

The practice sheet highlights cells where you should enter formulas. The same formulas must work when the tests replace the input data.

Challenge

Easy

Code E2 identifies a product in A2:A4. In F2, use INDEX with exact MATCH to return its name from B2:B4. All test keys exist.

Enter formulas in the highlighted output cells: F2. Keep the supplied data and headings. Tests change input values, so use cell references instead of typing the sample answers. Use English function names and commas between arguments.

Try it yourself

Sheet1