Excel Practice
Lessons Lesson 38

XLOOKUP

About this lesson

XLOOKUP takes the range to search and the range to return as two separate arguments. So it can look in any column and return any other.

By the end you can

  • look up a value with XLOOKUP, with nothing to count.
  • return from a column to the left of the one you searched.
  • give a lookup its own not-found message.

Practises XLOOKUP VLOOKUP INDEX MATCH IFNA

The idea

Three of VLOOKUP's problems go away at once. There is no column number to count, so inserting a column cannot break it. The return range is separate from the lookup range, so it can sit to the left. And exact match is the default, so you do not have to remember to ask for it.

The problem is availability. XLOOKUP arrived in Microsoft 365. It is not in Excel 2019 or earlier. A workbook that must open on an older install still needs INDEX and MATCH. Learn XLOOKUP for your own sheets, and INDEX with MATCH for other people's.

The mistake to watch for

Search and return columns that start on different rows. XLOOKUP pairs them row by row. So two columns of the same height that begin one row apart return the value from the wrong row, with no error. Start both ranges on the same row. And give the fourth argument something to say when the value is missing.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

XLOOKUP takes three things: what to find, the column to search, and the column to return from. There is no column number to count.

In E2, type =XLOOKUP(F2,A2:A5,D2:D5).

F2 is the code. A2:A5 is the codes to search. D2:D5 is the prices to return.

E2
Row A B C D E F G
1 Code Title Stock Price
2 BK-014 Meditations 4 9.99 BK-099
3 BK-241 Sapiens 12 10.99 Wolf Hall
4 BK-099 Wolf Hall 0 8.99 BK-888
5 BK-571 Normal People 7 12.5
6
7
8

Every step

  1. 1

    XLOOKUP takes three things: what to find, the column to search, and the column to return from. There is no column number to count. In E2, type =XLOOKUP(F2,A2:A5,D2:D5). F2 is the code. A2:A5 is the codes to search. D2:D5 is the prices to return.

    Hint. F2, A2:A5, D2:D5.

  2. 2

    Read =XLOOKUP(F2,A2:A5,C2:C5) and say what it returns. Same code, but the stock column is returned instead of the price.

    Hint. BK-099's row, the Stock column.

  3. 3

    The return column can sit to the left of the searched one, which VLOOKUP cannot do at all. F3 holds a title. In E3, find its code: search the titles in B2:B5 and return from the codes in A2:A5.

    Hint. Search B, return A.

  4. 4

    A fourth argument says what to show when nothing matches. F4 holds a code that is not on the list. In E4, look it up among the codes and return the title. Make the cell say Not stocked instead of an error.

    Hint. Fourth argument, in quotation marks.

  5. 5

    In E6, write a formula with XLOOKUP that works out what the shop's copies of Sapiens are worth. That is the stock times the price. Look up both by title.

    Hint. Stock lookup times price lookup.