XLOOKUP and INDEX with MATCH

VLOOKUP can only look to the right of its first column. Two more flexible tools:

XLOOKUP(what, look_in, return_from, if_not_found) finds what in one range and returns the matching cell from another, in any direction.
MATCH(what, range, 0) gives the position of the match, and INDEX(range, position) returns the value at a position. Together, INDEX(return_range, MATCH(what, look_range, 0)) does the same job.

In this course each line of an example is one cell: the address, a colon, then what is in the cell. A line starting with = is a formula. Press Run to see the sheet with every formula worked out.

Exampleexcel

Open in editor »

Quick check

What does `MATCH("B7", B2:B4, 0)` return in the sheet above?

2, the position of B7 in the rangeBooksB7

Your turn: write the code

Find the product name for the code in D1 using XLOOKUP and show it in E1.

Finish the quick check (and any code exercise) to complete this lesson, or mark it done yourself.

Tip: use the left and right arrow keys to move between lessons.