# Function XLOOKUP in Excel

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.

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])

## 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]))

## 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.

### 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.

And with the 4th argument

# Function XLOOKUP in Excel

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.

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])

## 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]))

## 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.

### 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.

And with the 4th argument