This tutorial introduces XLOOKUP, a new function in Excel for both vertical and horizontal lookups. Tasks that used to feel super complicated, like left-side lookups, finding the last match, or using VLOOKUP with multiple criteria, are now much easier with XLOOKUP.
Before, you had to choose between VLOOKUP for vertical lookups, HLOOKUP for horizontal ones, or more complex options like INDEX MATCH or Power Query. But now, you don’t have to pick anymore. XLOOKUP can handle all those tasks in one simple function.
So, why is XLOOKUP better? It can look up data vertically or horizontally, search to the left or above, use multiple criteria, and even return whole columns or rows of data, not just a single value. After over 30 years, Microsoft has created a function that fixes many of the issues with VLOOKUP.
The downside? XLOOKUP is only available in Excel for Microsoft 365, Excel 2021, and Excel for the web.
Table of Contents
Excel XLOOKUP function - syntax and uses
The XLOOKUP function in Excel helps you find a specific value in a range or list and returns a related value from another column. It can search both vertically (up and down) and horizontally (side to side). XLOOKUP can perform different types of searches: an exact match (the default), an approximate match (the closest value), or a wildcard match (a partial match).
Here’s the basic structure of how the XLOOKUP function is set up:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The first three parts of the XLOOKUP function are required and the last three are optional:
- Lookup_value – This is the value you want to find.
- Lookup_array – This is the range or list where you want to search for the value.
- Return_array – This is the range or list from which the function will return the related value.
Optional arguments: 4. If_not_found (optional) – This is what the function will return if no match is found. If you skip this, it will return an #N/A error. 5. Match_mode (optional) – This lets you choose the type of match:
- 0 or omitted (default) – An exact match is needed; if not found, #N/A is returned.
- -1 – Returns the exact match or the next smaller value if no exact match is found.
- 1 – Returns the exact match or the next larger value if no exact match is found.
- 2 – Uses wildcards (like * for partial matches).
- Search_mode (optional) – This controls the search direction:
- 1 or omitted (default) – Searches from the first value to the last.
- -1 – Searches backward, from the last value to the first.
- 2 – Uses a binary search on data sorted in ascending order.
- -2 – Uses a binary search on data sorted in descending order.
Note: The binary search is for advanced users. It’s a faster search method but works only when the data is already sorted correctly. Instead of checking each value one by one, it compares the middle value of the list to quickly find where the search value should be.
Basic XLOOKUP formula
To better understand how XLOOKUP works, let’s create a simple formula to perform an exact lookup. We’ll only need the first three parts of the function.
Imagine you have a table with information about the five oceans of the world. You want to find the area of a specific ocean, which is entered in cell G4 (this is your lookup_value). The ocean names are listed in cells B4
(this is your lookup_array), and their areas are listed in cells D4
(this is your return_array). Here’s the formula you would use:
=XLOOKUP(G4, B4:B8, D4:D8)
In simple terms, this formula says: “Look for the value in G4 in the range B4
, and return the matching value from D4
in the same row.” No need to worry about column numbers or sorting issues like you would with VLOOKUP – it just works!
If you prefer, you can directly type the ocean name (the lookup value) into the formula. For example:
=XLOOKUP(“Indian”, B4:B8, D4:D8)
This will search for “Indian” in the list of ocean names and return the corresponding area.
XLOOKUP availability
As of now, the XLOOKUP function is available in Excel 365, Excel 2021, and Excel for the web. It’s important to note that XLOOKUP is not backward compatible, meaning it won’t function in earlier versions.
How is XLOOKUP better than VLOOKUP?
XLOOKUP is a powerful function that surpasses VLOOKUP in many aspects. It offers more capabilities, versatility and efficiency. Here are the top 10 features that make it the ultimate lookup function in Excel:
- Vertical and horizontal lookup: The XLOOKUP function is named because it can search both up-and-down (vertically) and side-to-side (horizontally). This makes it more flexible than older functions like VLOOKUP and HLOOKUP, which could only search in one direction.
- Look in any direction: right, left, down, or up. Unlike VLOOKUP, which can only search in the leftmost column, and HLOOKUP, which only searches in the top row, XLOOKUP has no such limits. You can search in any direction—right, left, up, or down. This means doing a “left lookup,” which was tricky before, is now easy with XLOOKUP!
- Exact match by default: Most of the time, you’ll want an exact match, and XLOOKUP gives you that automatically. This is different from VLOOKUP, which by default looks for an approximate match. But if you ever need an approximate match, XLOOKUP can do that too!
- Partial match with wildcards: If you only know part of what you’re looking for not the whole thing you can use wildcards with XLOOKUP to find a partial match. This is useful when you don’t know the exact value but have a part of it, like searching for all items that start with “A” or contain a certain word.
- Search in reverse order: In the past, if you wanted to find the last occurrence of a value, you’d have to rearrange your data. With XLOOKUP, it’s much easier! Just set the search_mode to -1, and it will search from the end of the list, returning the last match automatically. No need to change the data order anymore!
- Return multiple values: With XLOOKUP, you can do more than just return a single value. By adjusting the return_array argument, you can retrieve an entire row or column of data related to your lookup value. This means XLOOKUP can return multiple pieces of information at once, not just one!
- Search with multiple criteria: XLOOKUP can easily handle multiple criteria because it works with arrays directly. This means you can look up values based on more than one condition at the same time, making your searches more powerful and flexible!
- If error functionality. Traditionally, we use the IFNA function to trap #N/A errors. XLOOKUP incorporates this functionality in the if_not_foundargument allowing to output your own text if no valid match is found.
- Column insertions/deletions. One of the most irritating issues with VLOOKUP is that adding or removing columns breaks a formula because the return column is identified by its index number. With XLOOKUP, you supply the return range, not number, meaning you can insert and remove as many columns as you need without breaking anything.
- Better performance. VLOOKUP could slow down your worksheets because it includes the entire table in calculations, which results in processing far more cells than actually needed. XLOOKUP handles only the lookup and return arrays that it truly depends on.
How to use XLOOKUP in Excel - formula examples
The following examples demonstrate the most useful XLOOKUP features in action. Additionally, you will discover a couple non-trivial uses that will take your Excel lookup skills to a new level.
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(E2, A3:A7, B3:B7)
For h-lookup, enter rows instead of columns:
=XLOOKUP(I1, B1:F1, B2:F2)
Left lookup performed natively
In older versions of Excel, using the INDEX MATCH formula was the only reliable way to look up values to the left or above. Now, you don’t need to combine two functions to do this; XLOOKUP can handle it all by itself. Just specify where you want to search, and XLOOKUP will find the value no matter where it is located.
For example, let’s say we have added a Rank column to the left of our sample table. If you want to find the rank of the ocean name entered in cell F1, VLOOKUP wouldn’t work here because it can only return values from columns to the right. But with XLOOKUP, you can easily get the rank without any trouble!
=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 way XLOOKUP finds matches is controlled by the fifth argument called match_mode. By default, it looks for an exact match.
It’s important to note that even when you set match_mode to look for an approximate match (using -1 or 1), XLOOKUP will still first try to find an exact match. The difference is what it returns if it can’t find an exact match.
Here’s how the match_mode argument works:
- 0 or omitted: Looks for an exact match; if it’s not found, you’ll get an #N/A error.
- -1: Looks for an exact match first; if it’s not found, it returns the next smaller item.
- 1: Looks for an exact match first; if it’s not found, it returns the next larger item.
Exact match XLOOKUP
This is the option you will likely use 99% of the time when doing lookups in Excel. Since XLOOKUP’s default setting is to look for an exact match, you can skip the **match_mode** argument and just use the first three required arguments.
However, there are times when an exact match won’t work. For example, your lookup table might not include every possible value but instead feature “milestones” or “bounds,” like discounts based on quantity or commissions based on sales.
In our sample lookup table, we show the relationship between exam scores and grades. As you can see in the screenshot below, an exact match only works when a student’s score matches a value in the lookup table exactly (like Christian’s score in row 3). In all other cases, you will get a #N/A error.
=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6)
To get the grades instead of #N/A errors, we need to look for an approximate match as shown in the next example.
Approximate match XLOOKUP
to do an approximate lookup, you can set the match_mode argument to either -1 or 1, depending on how your data is set up.
In our example, the lookup table shows the lower limits of the grades. So, we set match_mode to -1, which means the function will look for the next smaller value if it doesn’t find an exact match.
=XLOOKUP(F11, $B$11:$B$15, $C$11:$C$15, ,-1)
For example, Brian has the score of 98 (F2). The formula searches for this lookup value in B2:B6 but cannot find it. Then, it searches for the next smaller item and finds 90, which corresponds to grade A:
If our lookup table contained the upper bounds of the grades, we would set match_mode to 1 to search for the next larger item if an exact match fails:
=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6, ,1)
The formula searches for 98 and again cannot find it. This time, it tries to find the next larger value and gets 100, corresponding to grade A:
Tip. When copying an Xlookup formula to multiple cells, lock the lookup or return ranges with absolute cell references (like $B$2:$B$6) to prevent them from changing.
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
The direction of the search is controlled by the sixth argument called search_mode:
- 1 or omitted (default): Searches from the first value to the last, meaning top-to-bottom for vertical lookups or left-to-right for horizontal lookups.
- -1: Searches in reverse order, from the last value to the first.
For example, if you want to find the last sale made by a specific salesperson, you would use the first three required arguments (G1 for lookup_value, B2
for lookup_array, and D2
for return_array) and set the search_mode to -1. Your formula would look like this:
=XLOOKUP(G1, B2:B9, D2:D9, , ,-1)
It’s straightforward and easy, right?
XLOOKUP to return multiple columns or rows
Another amazing feature of XLOOKUP is that it can return more than one value related to the same match. You can do this using the standard formula without any extra steps!
For example, if you want to get all the details for the salesperson listed in cell F2, you just need to use a range that includes several columns (B2) for the return_array argument:
=XLOOKUP(F2, A2:A7, B2:D7)
You enter this formula in the top-left cell where you want the results to appear, and Excel will automatically fill in the results in the adjacent blank cells. In this case, the returned data (G2) includes one row and three columns (Date, Item, and Amount). So, you can think of it as XLOOKUP returning an entire row of information related to the lookup value!
If you prefer to 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))
To return values from non-adjacent columns, you can use the FILTER function inside the return_array of XLOOKUP. This way, you can specify which columns you want to include.
For example, you can use a horizontal array like {1,0,1} for the include argument of the FILTER function. In this array, the 1s tell Excel which columns to return, while the 0s indicate which columns to ignore. So, if you want to pull specific columns from your data, just set up the FILTER function like this within your XLOOKUP formula!
=XLOOKUP(F2, A2:A7, FILTER(B2:D7, {1,0,1}))
It should be noted, however, that XLOOKUP cannot return multiple columns when the lookup_value argument is a range. In this case, you can use the combination of the Chooserow and Xmatch functions:
=CHOOSEROWS(B2:C10, XMATCH(E2:E4, A2:A10))
You can also return values from multiple rows when doing a horizontal lookup. In other words, you can return an entire column of data that relates to your lookup value.
For example, if you want to get all the values from the Amount column, you would use “Amount” (found in cell F1) as your lookup_value. Then, use the range A1, which contains the column headers, as your lookup_array, and the range A2 as your return_array.
Your formula would look like this:
=XLOOKUP(F1, A1:D1, A2:D7)
This will return all the values from the Amount column that match your lookup value!
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:
XLOOKUP(1, (criteria_range1=criteria1) * (criteria_range2=criteria2) * (…), return_array)
How this formula works: Each condition (or criteria) test results in an array of TRUE and FALSE values. When these arrays are multiplied together, TRUE becomes 1 and FALSE becomes 0. The multiplication combines the results, so only the items that meet all the criteria are marked as 1 in the final lookup array. Since multiplying by 0 gives 0, only the rows that meet all the conditions will have a 1. Excel then finds the first “1” in the lookup array and returns the value from the corresponding position in the return array.
To see this in action, let’s retrieve an amount from the range D2
(return_array) based on these conditions:
- Criteria 1 (date) = G1
- Criteria 2 (salesperson) = G2
- Criteria 3 (item) = G3
With dates in A2
(criteria_range1), salesperson names in B2
(criteria_range2), and items in C2
(criteria_range3), the formula will look like this:
=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 / nested 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:
XLOOKUP(lookup_value1, lookup_array1, XLOOKUP(lookup_value2, lookup_array2, data_values))
How this formula works: The formula uses XLOOKUP’s ability to return an entire row or column. The inner XLOOKUP finds the lookup value and returns a row or column of related data. That data is then passed to the outer XLOOKUP as the return_array.
In this example, we want to find the sales made by a specific salesperson in a certain quarter. To do this, we enter the salesperson’s name in H1 and the quarter in H2. Then, we perform a two-way XLOOKUP using 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.
If Error XLOOKUP
When the lookup value isn’t found, XLOOKUP gives an #N/A error. While this makes sense to expert users, it can be confusing for beginners. To make it more user-friendly, you can add your own message in the 4th argument called if_not_found.
Let’s go back to the first example from this tutorial. If someone enters an invalid ocean name in cell E1, the following formula will display a clear message like “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:
XLOOKUP(TRUE, EXACT(lookup_value, lookup_array), return_array)
How this formula works: The EXACT function compares the lookup value with each value in the lookup array and returns TRUE if they are exactly the same, including the letter case. If they don’t match, it returns FALSE. This array of TRUE and FALSE values is used as the lookup_array in XLOOKUP. XLOOKUP then finds the TRUE value and returns the corresponding result from the return_array.
For example, to get the price from the range B2
(return_array) for the item in cell E1 (lookup_value), the formula in E2 would be:
=XLOOKUP(TRUE, EXACT(E1, A2:A7), B2:B7, “Not found”)
Excel XLOOKUP not working
If your formula does not work right or results in error, most likely it’s because of the following reasons:
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.
As you have just seen, XLOOKUP has many awesome features that make it THE function for almost any lookup in Excel. I thank you for reading and hope to see you on our blog next week!
