XLOOKUP function in Excel - powerful successor of VLOOKUP

Basic XLOOKUP formula

To gain more understanding, let's build an Xlookup formula in its simplest form to perform an exact lookup. For this, we will only need the first 3 arguments.

Supposing, you have a summary table with information about the five oceans on the Earth. You want to get the area of a specific ocean input in F1 (lookup_value). With the ocean names in A2:A6 (lookup_array) and areas in C2:C6 (return_array), the formula goes as follows:

=XLOOKUP(F1, A2:A6, C2:C6)

Translated into plain English, it says: search for the F1 value in A2:A6 and return a value from C2:C6 in the same row. No column index numbers, no sorting, no other ridiculous quirks of Vlookup! It just works :)

Look up vertically and horizontally

Microsoft Excel used to have two functions for different lookup types, each having its own syntax and usage rules: VLOOKUP to look vertically in a column and HLOOKUP to look horizontally in a row.

The XLOOKUP function can do both with the same syntax. The difference is in what you provide for the lookup and return arrays.

For v-lookup, supply columns:

=XLOOKUP(E1, A2:A6, B2:B6)

For h-lookup, enter rows instead of columns:

=XLOOKUP(I1, B1:F1, B2:F2)

Left lookup performed natively

In earlier versions of Excel, INDEX MATCH formula was the only reliable way to look to the left or above. Now, you no longer need to combine two functions where one would suffice. Just specify the target lookup array, and XLOOKUP will handle it without a problem regardless of its location.

As an example, let's add the Rank column to the left of our sample table. The goal is to get the rank of the ocean input in F1. VLOOKUP would stumble here because it can only return a value from a column to the right of the lookup column. An Xlookup formula copes with ease:

=XLOOKUP(F1, B2:B6, A2:A6)

In a similar manner, you can look above when searching horizontally in rows.

XLOOKUP with exact and approximate match

The match behavior is controlled by the 5^th argument called match_mode. By default, an exact match is performed.

Please pay attention that even when you choose an approximate match (match_mode set to 1 or -1), the function will still search for an exact match first. The difference is in what it returns if an exact lookup value is not found.

Match_mode argument:

• 0 or omitted - exact match; if not found - #N/A error.
• -1 - exact match; if not found - next smaller item.
• 1 - exact match; if not found - next larger item.

XLOOKUP with partial match (wildcards)

To perform a partial match lookup, set the match_mode argument to 2, which instructs the XLOOKUP function to process the wildcard characters:

• An asterisk (*) - represents any sequence of characters.
• A question mark (?) - represents any single character.

To see how it works, please consider the following example. In column A, you have a few smartphone models and, in column B, their battery capacity. You are curious about the battery of a certain smartphone. The problem is that you are not sure you can type the model name exactly as it appears in column A. To overcome this, enter the part that is definitely there and replace the remaining characters with wildcards.

For example, to get information about the battery of iPhone X, use this formula:

=XLOOKUP("*iphone X*", A2:A8, B2:B8, ,2)

Or, input the known part of the lookup value in some cell and concatenate the cell reference with the wildcard characters:

=XLOOKUP("*"&E1&"*", A2:A8, B2:B8, ,2)

XLOOKUP in reverse order to get last occurrence

In case your table contains several occurrences of the lookup value, you may sometimes need to return the last match. To have it done, configure your Xlookup formula to search in reverse order.

The direction of search is controlled be the 6^th argument named search_mode:

• 1 or omitted (default) - searches from first to last value, i.e. top-to-bottom with vertical lookup or left-to-right with horizontal lookup.
• -1 - searches in reverse order from last to first value.

As an example, let's return the last sale made by a specific salesperson. For this, we put together the first three required arguments (G1 for lookup_value, B2:B9 for lookup_array, and D2:D9 for return_array) and put -1 in the 5^th argument:

=XLOOKUP(G1, B2:B9, D2:D9, , ,-1)

Straightforward and easy, isn't it?

XLOOKUP to return multiple columns or rows

One more amazing feature of XLOOKUP is its ability to return more than one value relating to the same match. All is done with the standard syntax and without any extra manipulations!

From the below table, supposing you want to retrieve all the details pertaining to the salesperson of interest (F2). What you need to do is supply a range, not a singe column or row, for the return_array argument:

=XLOOKUP(F2, A2:A7, B2:D7)

You enter the formula in the top-left cell of the results range, and Excel automatically spills the results into adjacent blank cells. In our case, the return array (B2:D7) includes 3 columns (Date, Item and Amount), and all three values are returned into the range G2:I2.

If you'd rather arrange the results vertically in a column, nest XLOOKUP into the TRANSPOSE function to flip the returned array:

=TRANSPOSE(XLOOKUP(G1, A2:A7, B2:D7))

In a similar fashion, you can return an entire column of data, say the Amount column. For this, use cell F1 that contains "Amount" as lookup_value, the range A1:D1 containing the column headers as lookup_array and the range A2:D7 containing all data as return_array.

=XLOOKUP(F1, A1:D1, A2:D7)

XLOOKUP with multiple criteria

Another big advantage of XLOOKUP is that it handles arrays natively. Due to this ability, you can evaluate multiple criteria directly in the lookup_array argument:

How this formula works: The result of each criteria test is an array of TRUE and FALSE values. The multiplication of the arrays converts TRUE and FALSE into 1 and 0, respectively, and produces the final lookup array. As you know, multiplying by 0 always gives zero, so in the lookup array, only the items that meet all the criteria are represented by 1. And because our lookup value is "1", Excel takes the first "1" in lookup_array (first match) and returns the value from return_array in the same position.

To see the formula in action, let's pull an amount from D2:D10 (return_array) with the following conditions:

• Criteria1 (date) = G1
• Criteria2 (salesperson) = G2
• Criteria3 (item) = G3

With dates in A2:A10 (criteria_range1), salesperson names in B2:B10 (criteria_range2) and items in C2:C10 (criteria_range3), the formula takes this shape:

=XLOOKUP(1, (B2:B10=G1) * (A2:A10=G2) * (C2:C10=G3), D2:D10)

Though the Excel XLOOKUP function processes arrays, it works as a regular formula and is completed with a usual Enter keystroke.

The XLOOKUP formula with multiple criteria is not limited to "equal to" conditions. You are free to use other logical operators as well. For example, to filter orders made on the date in G1 or earlier, put "<=G1" in the first criterion:

=XLOOKUP(1, (A2:A10<=G1) * (B2:B10=G2) * (C2:C10=G3), D2:D10)

Double XLOOKUP

To find a value at the intersection of a certain row and column, perform the so-called double lookup or matrix lookup. Yep, Excel XLOOKUP can do that too! You simply nest one function inside another:

How this formula works: The formula is based on XLOOKUP's ability to return an entire row or column. The inner function searches for its lookup value and returns a column or row of related data. That array goes to the outer function as the return_array.

For this example, we are going to find the sales made by a particular salesperson within a certain quarter. For this, we enter the lookup values in H1 (salesperson name) and H2 (quarter), and do a two-way Xlookup with the following formula:

=XLOOKUP(H1, A2:A6, XLOOKUP(H2, B1:E1, B2:E6))

Or the other way round:

=XLOOKUP(H2, B1:E1, XLOOKUP(H1, A2:A6, B2:E6))

Where A2:A6 are the salesperson names, B1:E1 are quarters (column headers), and B2:E6 are data values.

A two-way lookup can also be performed with an INDEX Match formula and in a few other ways. For more information, please see Two-way lookup in Excel.

If Error XLOOKUP

When the lookup value is not found, Excel XLOOKUP returns an #N/A error. Quite familiar and understandable to expert users, it might be rather confusing for novices. To replace the standard error notation with a user-friendly message, type your own text into the 4^th argument named if_not_found.

Back to the very first example discussed in this tutorial. If someone inputs an invalid ocean name in E1, the following formula will explicitly tell them that "No match is found":

=XLOOKUP(E1, A2:A6, B2:B6, "No match is found")

Case-sensitive XLOOKUP

By default, the XLOOKUP function treats lowercase and uppercase letters as the same characters. To make it case-sensitive, use the EXACT function for the lookup_array argument:

How this formula works: The EXACT function compares the lookup value against each value in lookup array and returns TRUE if they are exactly the same including the letter case, FALSE otherwise. This array of logical values goes to the lookup_array argument of XLOOKUP. As the result, XLOOKUP searches for the TRUE value in the above array and returns a match from the return array.

For example, to get the price from B2:B7 (return_array) for the item in E1 (lookup_value), the formula in E2 is:

=XLOOKUP(TRUE, EXACT(E1, A2:A7), B2:B7, "Not found")

XLOOKUP is not available in my Excel

The XLOOKUP function is not backward compatible. It's only available in Excel for Microsoft 365 and Excel 2021, and won't appear in earlier versions.

XLOOKUP returns wrong result

If your obviously correct Xlookup formula returns a wrong value, chances are that the lookup or return range "shifted" when the formula was copied down or across. To prevent this from happening, be sure to always lock both ranges with absolute cell references (like $A$2:$A$10).

XLOOKUP returns #N/A error

An #N/A error just means the lookup value is not found. To fix this, try searching for approximate match or inform your users that no match is found.

XLOOKUP returns #VALUE error

A #VALUE! error occurs if the lookup and return arrays have incompatible dimensions. For instance, it is not possible to search in a horizontal array and return values from a vertical array.

XLOOKUP returns #REF error

A #REF! error is thrown when looking up between two different workbooks, one of which is closed. To fix the error, simply open both files.