Microsoft added the XLOOKUP function to Excel in 2019. Initially, it was only available in Office 365 (now Microsoft 365) versions of Excel but was also added to the standalone Office 2021 version.
XLOOKUP is a better choice compared to the older lookup functions like VLOOKUP, HLOOKUP and LOOKUP in Excel. This is because it’s more versatile, flexible, and straightforward to use.
We walk you through everything you need to know to put the XLOOKUP function to work in this guide. By the end of this article, you’ll be confidently using XLOOKUP in your day-to-day work.
Table of Contents
- How to Use the XLOOKUP Function
- XLOOKUP Vertically and Horizontally
- XLOOKUP Right to Left
- XLOOKUP with Exact and Appropriate Match
- Exact match XLOOKUP example
- Appropriate match XLOOKUP example
- XLOOKUP With Wildcards
- XLOOKUP Last Match
- XLOOKUP and Return Multiple Values (Row or Columns)
- XLOOKUP with Multiple Conditions
- Things to Consider Before Using the XLOOKUP FUnction in Excel
- Frequently Asked Questions
- Conclusion
The XLOOKUP Function
The XLOOKUP function is an improved version of various Excel LOOKUP functions. For example, you use it to search a specific range or an array of a specified value in a worksheet, both vertically and horizontally. And it returns the corresponding value on another column.
Here is the syntax with arguments:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- Lookup_value – Specifies the value to search for.
- Lookup_array – Denotes the cell range where the function has to search.
- Return_array – Specifies the range or array to extract the values.
- [if_not_found] – This is optional, and it is the value or message to return if there’s no match. If not used, and an error is found, the formula returns a #N/A error.
- [match_code] – This is optional, and you can specify 0, -1, 1, or 2 depending on the match type you want to perform.
- [search_mode] – This too is optional, and you specify 1, -1, 2, or -2 depending on the direction of the search.
Now let’s look at some examples to see how the XLOOKUP function works.
XLOOKUP Vertically and Horizontally
The XLOOKUP function, unlike other lookup functions, can work vertically and horizontally with the same syntax. Therefore, you only need to ensure that what you provide for the lookup and the return array is correct to get the desired result.
In this example, we are doing a vertical lookup, looking up information in array A2 to A6 and returning the result in array B2 to B6 based on the search parameter in E1.
=XLOOKUP(E1, A2:A6, B2:B6)
Similarly, we can replace our columns with rows, and the XLOOKUP function can handle that as well:
=XLOOKUP(I1, B1:F1, B2:F2)
XLOOKUP Right to Left
Earlier versions of Excel didn’t have many reliable ways to lookup values to the left of or above the lookup_array. The only reliable method was using the Index Match formula. Unfortunately, this required users to combine two formulas, which, to be honest, added complexity. Microsoft has now changed with XLOOKUP.
The XLOOKUP function does the same task without relying on anything else. You simply need to specify the target lookup array to get started.
In this example, we’re searching in column B for our match and then looking up the result to the left (column A):
=XLOOKUP(F1,B2:B6,A2:A6)
XLOOKUP With Exact and Approximate Match
The 4th argument in XLOOKUP controls match behaviour. In XLOOKUP, the default is to return an exact match (if one is found) or an approximate match (if one cannot be found). It’s essential to understand this before using the match mode argument.
First, XLOOKUP searches for an exact match by default. That means it’s trying to match what is in the cell exactly. If XLOOKUP can’t find an exact match, it moves to choose an approximate match. You can dictate what kind of approximate match XLOOKUP finds based on whether you use 0, -1, 1, or 2.
Here are the results it can return:
| Search_mode | If found | If not found |
|---|---|---|
| 0 | returns the exact match(default) | #N/A error |
| -1 | returns the exact match | next smallest value |
| 1 | returns the exact match | next largest value |
| 2 | returns the exact match | partial match |
Exact match XLOOKUP example
Most of the time, you will use XLOOKUP to find an exact match. In this instance, there’s no need to include the match_mode argument. The first three required arguments of the XLOOKUP function are enough to get you the results you expect.
In the example below, we show you what happens when the exact match occurs. This example is a correlation between exam scores and the grades of various students. If the score matches B2, then it returns the corresponding grade. Other scores that don’t match will return an #NA. For example, 98 by Brian in F2 returns #N/A instead of grade A.
The exact match will only find a student whose score matches a score indicated on the table (in this case, Christian).
The other cases that do not have an exact match in the table will return an #N/A error.
The exact match formula here is:
=XLOOKUP(F2,$B$2:$B$6,$C$2:$C$6).
Approximate match XLOOKUP example
If we want XLOOKUP to show us the closest grades instead of an #N/A, then we need to use an approximate match.
First, you need to look at the organization of the data and then set your argument to either -1 or 1 to return the next smallest value or the next largest value, respectively.
In our example, the only way to find the values we need is to set match_mode to -1. This is because the table lists the lower bounds of the students’ grades. Here’s how to apply the syntax in such a case:
=XLOOKUP(F2,$B$2:$B$6,$C$2:$C$6,,-1)
In the example below, we focus on Brian, who scored 98, as shown in F2. First, our formula searches for the match value in cell B2:B6, but it doesn’t find it there. So it then searches for the next smaller value closest to Brian’s score and finds 90, an A.
You can also apply this formula to find the grades for Colin, Laura, and Peter. Be sure to lock the lookup and return ranges with absolute cell references when copying the XLOOKUP function to multiple cells to prevent your cell references from changing.
In the example below, we have a table with the upper bounds of the students’ grades. Here we’ll search for the next largest item, which means we set the match_mode to 1. Here’s how we’ll apply the syntax:
=XLOOKUP(F2,$B$2:$B$6,$C$2:$C$6,,1).
The formula attempts to look for 98 but cannot find it in the table. So, it searches for the next largest value, in this case, 100. Brian scored 98, so he’s assigned an A.
XLOOKUP With Wildcards
You can also use a partial match in an XLOOKUP. These are known as wildcards. In the example below, we are trying to find the battery capacity of an iPhone X. We enter the model name (iPhone X) in the syntax to find its battery capacity.
The formula is =XLOOKUP(“*”&E1&”*”,A2:A8, B2:B8,,2).
Let’s look at what is going on here. In this example, the 5th argument is 2, which is a wildcard match. We’ve also added an asterisk in inverted commas (“*”) before and after the lookup_value. This tells Excel there may be more text before or after our lookup_value.
The lookup_value, ‘iPhoneX’ is in cell E1 and the lookup_array is A2:A8, and the return_array is B2:B8.
Even though we’re only providing part of the information for the lookup_value, because we added the wildcard condition as the fifth argument (2), XLOOKUP is returning the correct result. In this case, 2716.
XLOOKUP Last Match
You may want to return the last match where your table contains several occurrences of the same data. In this case, the search direction depends on the 6th argument (search_mode). For example, you can use 1 to search from first to the last or -1 to search in reverse order from last to the first.
Here’s the formula to use in this case:
=XLOOKUP(C11,B2:B9,D2:D9,,,-1).
Below, we show you how to apply this formula with C11 as the lookup_value, B2:B9 the lookup_array, D2:D9 the return_array and -1 for the 5th argument.
In this example we are looking for a salesperson, ‘Laura’. Her first sale was on the 2nd of September. Her second and third sales were on the 3rd of September. Here, we are trying to extract the total for her last sale on the 3rd. Using our last match lookup, we get $45.
XLOOKUP and Return Multiple Values (Row or Column)
XLOOKUP can also return more than one value relating to the same match. To achieve this, you still just need the XLOOKUP function, but be warned – make sure there are enough cells to the right and below as multiple values will populate neighbouring cells.
Below, we are trying to return the Item and the Amount for the salesperson, Colin. We will achieve this by supplying a range across two columns for the return argument. Here’s the formula to help us do this:
=XLOOKUP(A10,A2:A7,C2:D7)
XLOOKUP With Multiple Conditions
You can also evaluate multiple criteria using the XLOOKUP function. The formula allows you to use other logical operators without limiting you to ‘equal to’ conditions. Let’s say these are our conditions:
- Criteria1 (date) = G1
- Criteria2 (salesperson) = G2
- Criteria3 (item) = G3
Below we have the formula and an example showing you how to apply these examples:
=XLOOKUP(1,(A2:A9=G1)*(B2:B9=G2)*(C2:C9=G3),D2:D9).
The dates are in A2:A9 (criteria_range1), the salesperson names in B2:B9 (criteria_range2) and items in C2:C9 (criteria_range3).
Things to Consider Before Using the XLOOKUP Function in Excel
As you can see, XLOOKUP is awesome and far more powerful and flexible than its predecessors, VLOOKUP, HLOOKUP, and LOOKUP. Before you start using it, here are a few XLOOKUP top tips :
- Don’t forget XLOOKUP Excel can use data both horizontally and vertically.
- XLOOKUP can return one or more results depending on the formula used.
- You can get a custom error message if you use the not_found argument with no matching result. Otherwise, the function will return an #N/A error.
- Ensure the return_array is the same size as the lookup_array.
- You can open two spreadsheets and use XLOOKUP to return values between them. You’ll get a #REF! Error if one spreadsheet is closed.
- This function doesn’t return a cell reference as a value but as a result.
- You’ll get a #NAME error if looking for XLOOKUP in an Excel version that doesn’t support it (It’s only available on Microsoft 365 Excel, Excel 2021 and later).
Frequently Asked Questions
1. How do I use XLOOKUP if the searched value is not found?
The XLOOKUP function can handle the #N/A error as any other LOOKUP function. By default, the XLOOKUP function returns the exact match. In the case that a match cannot be found in the lookup_table, XLOOKUP supports an optional parameter called not_found, which overrides the #N/A error.
2. Why am I getting a #NAME error when using XLOOKUP?
If you are getting a #NAME error then unfortunately, the version of Excel you are using doesn’t support XLOOKUP.
Conclusion
That’s all you need to know about the XLOOKUP Excel formula and how to use it. If you have XLOOKUP available in your version of Excel, we definitely recommend using it over VLOOKUP or INDEX MATCH!
