Excel Function: XLOOKUP

The Excel function XLOOKUP searches for a value in a table and then returns the corresponding value (at the same position) in a second table.

This function is typically used to search for a value in one column of a table and return the corresponding value from another column.

The XLOOKUP function is much more functional than the VLOOKUP function which only searches for the value in the first column of a table, and it's simpler to use than the combination INDEX + MATCH which requires using 2 functions.


=XLOOKUP(lookup_value, lookup_array, return_array)


=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)

Example of Use

The objective here is to search for information based on the city name. The user should be able to enter the city name in the green part and then see the result of his search in the blue part:

excel function table xlookup

For now, it's the same example as the one on the INDEX + MATCH functions page, but this time, realized using the sole XLOOKUP function.

Select the XLOOKUP function and enter:

excel function xlookup

The case number is then displayed:

excel function xlookup search table

To then display the points, you just need to copy the formula and modify Return_array:

excel function xlookup tutorial table

Optional Arguments

In the previous example, only the 3 mandatory arguments were entered in the XLOOKUP function, but there are 3 more that are optional.

The most useful is certainly If_not_found which allows you to specify the value to return if there is no result and thus avoid the #N/A error.

For example:

excel function xlookup no result

The second optional argument Match_mode allows you to choose the search mode although most of the time, you will only use the default mode (0):

For example, with mode 2 and search M* (or m*), the function will search for the first city whose name begins with M:

excel function xlookup starts with

And finally, the last optional argument is Search_mode:

Multiple Values

It is possible to return several values at once with a single XLOOKUP function.

For example, to return the entire line, enter the entire table in Return_array:

excel function xlookup multiple values
If needed, you can download the Excel file used here: xlookup.xlsx
The XLOOKUP function is only available with Excel 365. If you have an older version of Excel, use the combination of functions INDEX + MATCH.