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

By Ali, Founder, Excel Lessons

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, and it is in Excel 2021 and Excel 2024 as well. 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.

Read more about this

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 search column, and the return column.

In E2, type:

=XLOOKUP(F2,A2:A5,D2:D5)

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

Remember: there is no column number to count.

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 search column, and the return column. In E2, type: =XLOOKUP(F2,A2:A5,D2:D5). F2 is the code, A2:A5 the codes to search, D2:D5 the prices to return. Remember: there is no column number to count.

    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 F3's code: search B2:B5, return 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 up F4's code and return its title. Show Not stocked if the code is missing.

    Hint. Fourth argument, in quotation marks.

  5. 5

    In E6, find what the shop's Sapiens copies are worth. Shape: =XLOOKUP(lookup,search,return)*XLOOKUP(lookup,search,return). That is stock times price.

    Hint. Stock lookup times price lookup.