Function XLOOKUP in Excel

Function XLOOKUP in Excel
Last Updated on 21/04/2024
Reading time: 2 minutes

How to build XLOOKUP in Excel

  1. The first argument is the lookup value

    Only one cell is expected

  2. Where to search the lookup value

    Select only one column

  3. Where is the value to return

    Select only one column

XLOOKUP was released only for Excel 365 in 2020. This function simplifies the way to look up data in a table.

Why the name XLOOKUP? There is a reason.

For many years, two Excel functions could look up inside a table

But both functions have their pros and cons.

FunctionsProsCons
VLOOKUP Fairly simple to buildThe lookup column is always the first one
INDEXLookup in any direction
- horizontal
- vertical
- and in any column, even before the lookup column
Lookup by position value (3rd row, 5th column) and not by value

XLOOKUP keeps the pros of these two functions and removes the cons.

And also XLOOKUP name combines the two other functions' names.

  • X comes from the indeX function
  • LOOKUP comes from vLOOKUP function

How to lookup for value with XLOOKUP?

For instance, we want to return the area according to the country name. The data are inserted in a Table. For that, the reference of the formula will display the column name of the Table. It's easier to read

=XLOOKUP(C11,tbl_Country[Country],tbl_Country[Area])

Build Excel XLOOKUP with only 3 arguments

Lookup to the left

Next, you want to return the Capital name. But the Capital column is on the left of the Calling Code (the lookup column).

Performing a lookup to the left is impossible with VLOOKUP but with XLOOKUP, it's very easy to do.

=XLOOKUP(C11,tbl_Country[Calling code],tbl_Country[Capital]))

XLOOKUP function returns a value to the left

The improvement over other functions.

Return more than 1 column.

In Excel 365, the calculation engine can now return the result in multiple cells. In this context, the XLOOKUP function can

For instance, according to the country name, we return to the Area and the Population.

XLOOKUP function returns more than one column

Manage #N/A (Unknown value)

When an unknown value is a lookup, VLOOKUP and INDEX return #N/A. But now, XLOOKUP can return a custom result if the value is unknown.

In this situation, you must fill the 4th argument of the XLOOKUP function of Excel.

XLOOKUP 4th argument missing

And with the 4th argument

XLOOKUP with the fourth argument

Leave a Reply

Your email address will not be published. Required fields are marked *

Function XLOOKUP in Excel

Reading time: 2 minutes
Last Updated on 21/04/2024

How to build XLOOKUP in Excel

  1. The first argument is the lookup value

    Only one cell is expected

  2. Where to search the lookup value

    Select only one column

  3. Where is the value to return

    Select only one column

XLOOKUP was released only for Excel 365 in 2020. This function simplifies the way to look up data in a table.

Why the name XLOOKUP? There is a reason.

For many years, two Excel functions could look up inside a table

But both functions have their pros and cons.

FunctionsProsCons
VLOOKUP Fairly simple to buildThe lookup column is always the first one
INDEXLookup in any direction
- horizontal
- vertical
- and in any column, even before the lookup column
Lookup by position value (3rd row, 5th column) and not by value

XLOOKUP keeps the pros of these two functions and removes the cons.

And also XLOOKUP name combines the two other functions' names.

  • X comes from the indeX function
  • LOOKUP comes from vLOOKUP function

How to lookup for value with XLOOKUP?

For instance, we want to return the area according to the country name. The data are inserted in a Table. For that, the reference of the formula will display the column name of the Table. It's easier to read

=XLOOKUP(C11,tbl_Country[Country],tbl_Country[Area])

Build Excel XLOOKUP with only 3 arguments

Lookup to the left

Next, you want to return the Capital name. But the Capital column is on the left of the Calling Code (the lookup column).

Performing a lookup to the left is impossible with VLOOKUP but with XLOOKUP, it's very easy to do.

=XLOOKUP(C11,tbl_Country[Calling code],tbl_Country[Capital]))

XLOOKUP function returns a value to the left

The improvement over other functions.

Return more than 1 column.

In Excel 365, the calculation engine can now return the result in multiple cells. In this context, the XLOOKUP function can

For instance, according to the country name, we return to the Area and the Population.

XLOOKUP function returns more than one column

Manage #N/A (Unknown value)

When an unknown value is a lookup, VLOOKUP and INDEX return #N/A. But now, XLOOKUP can return a custom result if the value is unknown.

In this situation, you must fill the 4th argument of the XLOOKUP function of Excel.

XLOOKUP 4th argument missing

And with the 4th argument

XLOOKUP with the fourth argument

Leave a Reply

Your email address will not be published. Required fields are marked *