Microsoft Excel’s Versatile XLOOKUP Function
Introduction
Microsoft Excel has several functions categorized as lookup functions. Many of them can be replaced by the versatile XLOOKUP function. The XLOOKUP function can replace the VLOOKUP, HLOOKUP, and INDEX + MATCH functions. However, the XLOOKUP function is only available in Excel for Microsoft 365. If you have an older Excel version, you’ll need to use one of the other Excel lookup functions to perform your lookups.
Sample Data List
Throughout this article, we’ll be using the list of climate data for Tampa, FL below as our data source (source):
You will see “Go to Data Source” links throughout the article, which will bring you back to the image above.
TL;DR
If you just want the quick facts about the XLOOKUP function, here are the basic details.
You use the XLOOKUP function by passing it:
- Lookup Value — A value you want to look for.
- Lookup Array — A range of cells or an array to search for the value within.
- Return Array — A range of cells or an array to return a value from.
For example, to return the Avg. High for March from our climate data list:
- Select the cell where you want the result of the formula.
- Type =XLOOKUP(“March”,A4:A15,B4:B15).
- Press Enter.
The value of “76” will be returned to the cell where you entered the formula.
There are three arguments used in the XLOOKUP function above:
- Lookup Value: March
- Lookup Array: A4:A15 (where the months are located)
- Return Array: B4:B15 (where the Avg. High temperatures are located)
To learn more about the XLOOKUP function, its optional arguments, and to see examples, continue reading.
Examples
You can use the links below to jump to any of the examples in this article:
Required Arguments
Lookup Value Not Found
Match Modes
Search Modes
Other Function Details
Sample Workbook
The best way to learn a new function is to try it out yourself. You can try out all the examples in this article by downloading this Excel file or by retyping the data from Figure 1 into your own worksheet. You could also use your own data, just be aware that you’ll need to change the cell references you use in your formulas.
What is the XLOOKUP Function Used For?
The XLOOKUP function is used to search for a known value in a data list and return an unknown value. Within our climate data list, we have the months of the year, average high temperatures, average low temperatures, and average precipitation for each month.
We could use the XLOOKUP function to return the average high temperature for a specific month. The month name is the known value that we will search for, and the average high temperature is the unknown value that will be returned from the function.
XLOOKUP Syntax
If you look at the Microsoft documentation on the XLOOKUP function, you’ll see something like this:
This looks a bit technical, but it’s really not that bad if you look at each part and understand what it represents:
- The equal sign (=) starts the formula.
- “XLOOKUP” is the function name.
- The opening parenthesis indicates that you are going to start passing data to the function.
- Within the parentheses, there are six arguments with a comma after each one.
- The first three arguments of the function are not contained within square brackets ([]), indicating they are required.
- The last three arguments of the function are contained within square brackets, indicating they are optional.
- The closing parenthesis ends the function.
Here is a description of the six arguments you can pass to the XLOOKUP function:
- lookup_value — (Required) The value you are searching for.
- lookup_array — (Required) The range of cells or an array where you look for the lookup_value.
- return_array — (Required) The range of cells or an array you are returning a value from.
- if_not_found — (Optional) The text to return if the lookup_value is not found within the lookup_array.
- match_mode — (Optional) The type of match to use.
- search_mode — (Optional) The type of search to perform.
There are four Match Modes:
- 0 — Exact match (default).
- -1 — Exact match or the next smallest value.
- 1 — Exact match or the next largest value.
- 2 — Wildcard match.
For Match Mode 2 (wildcard match), there are three wildcard characters you can use:
- * — (asterisk) Represents any number of characters.
- ? — (question mark) Represents a single character, can be used multiple times.
- ~ — (tilde) Used to match an asterisk, question mark, or tilde.
There are four Search Modes:
- 1 — Search from the first item in the lookup_array to the last item in the lookup_array. (default)
- -1 — Search from the last item in the lookup_array to the first item in the lookup_array.
- 2 — Binary search, lookup_array must be sorted in ascending order.
- -2 — Binary search, lookup_array must be sorted in descending order.
The image below diagrams the arguments of the XLOOKUP function:
Performing Vertical Lookups
The XLOOKUP function can be used to look for a value (lookup_value) in a column of cells (lookup_array). A value is returned from another column of cells (return_array) from the same row as the Lookup Value. This is considered a vertical lookup because the Lookup Value and Returned Value are in the same row vertically across the worksheet.
So, if we wanted to return the Avg. High for March from our sample data:
- The Lookup Value would be “March”.
- The Lookup Array would be “A4:A15”.
- The Return Array would be “B4:B15”.
The XLOOKUP function would perform its work by looking for “March” within the Lookup Array. If the value is found, the row it was found in would be used to return a value from the Return Array. If the value is not found, the #N/A error would be returned.
Since “March” is in cell A6 of our sample data, the value returned from the XLOOKUP function would be in cell B6, which is the Avg. High of 76.
Keep in mind that the Lookup Array and Return Array must have the same number of rows, or you will receive a #VALUE! error as a result of the function. It makes sense that you would use the same number of rows in both arrays because the Lookup Value could be in any row of the Lookup Array, so we would need to return a value from any of those same rows within the Return Array.
Also, be aware that using the XLOOKUP function to perform a vertical lookup is very similar to using the VLOOKUP function.
The animated GIF below demonstrates how the XLOOKUP function performs a vertical lookup:
Performing Horizontal Lookups
The XLOOKUP function can also be used to look for a value (lookup_value) in a row of cells (lookup_array). A value is returned from another row of cells (return_array) from the same column as the Lookup Value. This is considered a horizontal lookup because the Lookup Value and Returned Value are in the same column horizontally down the worksheet.
So, if we wanted to return the Avg. Low for the first row of our sample data:
- The Lookup Value would be “Avg. Low”.
- The Lookup Array would be “B3:D3”.
- The Return Array would be “B4:D4”.
The XLOOKUP function would perform its work by looking for “Avg. Low” within the Lookup Array. If the value is found, the column it was found in would be used to return a value from the Return Array. If the value is not found, the #N/A error would be returned.
Since “Avg. Low” is in cell C3 of our sample data, the value returned from the XLOOKUP function would be in cell C4, which is the Avg. Low in the first row of data or 52.
Keep in mind that the Lookup Array and Return Array must have the same number of columns, or you will receive a #VALUE! error as a result of the function. It makes sense that you would use the same number of columns because the Lookup Value could be in any column of the Lookup Array, so we would need to be able to return a value from any of those same columns within the Return Array.
Also, be aware that using the XLOOKUP function to perform a horizontal lookup is very similar to using the HLOOKUP function.
The animated GIF below demonstrates how the XLOOKUP function performs a horizontal lookup:
We’ve looked at how the XLOOKUP function works. Let’s look at some specific examples to better understand the required and optional arguments of the XLOOKUP function.
Required Arguments
Passing a Text Value as the Lookup Value
For our first example, we’ll use our climate data to perform a lookup using the three required arguments of the XLOOKUP function.
Suppose you wanted to return the Avg. High temperature for January, you would need to supply the following arguments to the XLOOKUP function:
- Lookup Value = “January”
- Lookup Array = A4:A15
- Return Array = B4:B15
Here is what the formula would look like using the three arguments:
We want to find the Avg. High temperature for January, so we set the first argument of the XLOOKUP function, the Lookup Value, to the text “January”. Anytime you include text as a function’s argument, you need to include it within double quotation marks; otherwise, Excel will think the text is a named range. If the named range does not exist, you will get a #NAME? error.
Next, we need to specify where Excel should look for the text “January”, so we set the second argument, the Lookup Array, to A4:A15. This is the range of cells that contain the month names in the data.
Finally, we need to specify the third required argument, the Return Array, where Excel returns a value. We want to return the Avg. High temperature for January, which is in column B of our data. The Return Array needs to contain all the same rows that our Lookup Array contained, so the Return Array is B4:B15.
Now that we know all the arguments we want to use for the XLOOKUP function, we need to select the cell where we want the result to be displayed and type the formula.
In Figure 8, cell G3 contains the formula:
The result of =FORMULATEXT(G4) is the text of the formula in cell G4. I have used the FORMULATEXT function throughout this article, so you can see the XLOOKUP formulas I’m using without having to look at the formula bar.
Also, in Figure 8, cell G4 contains the result of the example XLOOKUP function.
Figure 9 shows the climate data highlighting that the XLOOKUP found “January” in cell A4, then used the same row number to return the result of “70” from cell B4.
Passing a Cell Reference as the Lookup Value
We typed specific text into the lookup formula (January) as the Lookup Value in the previous example. Another way of using the XLOOKUP function is to pass a cell reference that contains the text you want to look up as the Lookup Value instead of specific text.
So, we could use an XLOOKUP function again to look for the Avg. High temperature for January, but this time, we’ll type the text “January” in a cell and reference that cell in the XLOOKUP function.
Figure 10 displays the formula used to accomplish this lookup in cell G6, along with the Lookup Value we want to use in cell G7, and the result of the XLOOKUP function in cell G8:
Notice that the first argument in the XLOOKUP function is “G7”, this is the cell where we typed “January,” and it is being used as the Lookup Value of the function.
The nice thing about doing the lookup this way is that you don’t have to update the formula if you want to look for another month’s Avg. High, you will just change the text in cell G7 from “January” to the month that you want to look for.
Lookup Value Not Found
The Result is an Excel Error
As you work with the XLOOKUP function, you may run across instances where the value you search for does not exist within your data. When this happens, the XLOOKUP function will return the #N/A error.
In the following example, I try to look for the text “ABC” in the Month column of the data list. Since there is no month called “ABC”, an error is returned.
Figure 11 shows the formula, Lookup Value, and result of looking for the month “ABC” from our data:
The Result is the If Not Found Argument Value
With other lookup functions, you would have to use another function within the lookup function to catch the error and display some specific text instead of the Excel error message. However, the XLOOKUP function has an argument that you can use to specify the result to return if the Lookup Value cannot be found.
The XLOOKUP function’s fourth argument is called the If Not Found argument. You can use the argument to specify the text to return if the Lookup Value in the function is not found in the Lookup Array. You can type the text to return in double-quotes or use a cell reference containing the text to return.
In Figure 12, we tried to look for the month “ABC” again, but we included the text string “No match.” as the If Not Found argument.
The returned result from this formula is “No match.”, which is a more user-friendly result than the #N/A error.
Match Modes
The fifth argument of the XLOOKUP function is the Match Mode. This argument determines how Excel looks for the Lookup Value. There are four match modes. Each Match Mode has a code you use to represent the mode.
Here are the four possible match modes:
If you omit this argument from your formula, Excel will use the Exact match mode.
Let’s look at some examples of using the Match Mode argument.
Exact Match
If you leave off the Match Mode argument of the XLOOKUP function or if you set it to “0”, Excel will attempt to find an exact match of the function’s Lookup Value within the Lookup Array. If an exact match cannot be found, the function will return the #N/A error or the value specified in the If Not Found argument.
All the previous XLOOKUP examples in this article did not include the Match Mode argument, so Excel performed an Exact match. Figure 13 is another example of an XLOOKUP function that does not include the Match Mode:
Since the month of “March” exists in the Lookup Array, the Avg. High temperature for March was returned as the result in cell G9.
You can also be explicit and use the Match Mode of “0” to perform an Exact match. Figure 14 shows an example of doing this.
We get the same result from this XLOOKUP function as from the previous example because “March” is a month in the Lookup Array.
Exact or Smaller
In addition to the Exact match option, you can look for an exact value or the next smallest value. As an example of doing this, let’s say that you wanted to return the earliest month in the year that averages a high temperature of 85 or the next closest temperature. To do this, we would use the XLOOKUP function passing the following arguments:
Figure 15 shows what the formula, Lookup Value, and result would look like:
I’ve temporarily sorted the data in our data list by Avg. High in ascending order so you can get a better understanding of why the function returned “October”:
Since there is no Avg. High temperature of 85 in our list of data, Excel looks for the next smallest temperature, in this case, October’s Avg. High is 84, so “October” is returned from the formula.
Interestingly, in the formula in Figure 15, the Lookup Array is in column B (B4:B15), and the Return Array is in column A (A4:A15). If you have ever used the VLOOKUP function, you’ll know that this is something you cannot do. With the VLOOKUP function, you specify one array that contains the Lookup Value and the Return Value, and the Lookup Value must be in the first column of the array.
With the XLOOKUP function, there is an array for the Lookup Value and an array for the Returned Value, so it doesn’t matter which column each array is referencing.
Exact or Larger
Another Match Mode option is to look for an exact match or the next largest match. If we search for an Avg. High temperature of 85 again, but use the Match Mode for Exact or Larger, the month with the next highest temperature will be returned:
In Figure 17, we see that “May” was returned because there is no 85 or 86 temperature in the Avg. High column, but May’s Avg. High is 87, so “May” is returned.
Wildcard Matches
If you’re not sure of the exact text you want to use as your Lookup Value, you have the option to use the Wildcard Match Mode. With this Match Mode, you use wildcard characters within the Lookup Value to represent unknown characters.
Wildcard characters can be used to represent one or more characters. There are three wildcard characters you can use:
Let’s look at some examples of using wildcard characters in our Lookup Values.
In the first example, we’ll return the Avg. High temperature for the first month that starts with the characters “Ma”. To do this, we’ll specify “Ma*” as the Lookup Value and use a “2” for the Match Mode.
Figure 18 shows what the formula would look like:
Since March is the first month that matches the pattern of starting with the characters “Ma”, and it has additional characters in the month name, the Avg. High temperature for March is returned.
As another example, let’s try out the question mark wildcard. If you wanted to return the Avg. High temperature for the first month that starts with “Ma” but only has one more character in the month name, you would use this formula:
In this case, the Avg. High temperature for May is returned because May is the first month that starts with the characters “Ma” and has only one more character in its name. March’s Avg. High would not be returned in this case because there is more than one more character in the month’s name after the “Ma”.
As a final wildcard example, if our data had values with asterisks (*), question marks (?), or tildes (~), we could preface those characters with a tilde to search for them. Figure 20 shows an example of doing this:
We do not have any data with an asterisk in the text, but if we wanted to search for “Ma*” we need to use “Ma~*” as the Lookup Value so that Excel knows we want to look for the asterisk character. If we didn’t do this, Excel would assume that we wanted to look for a value that started with “Ma” and had some additional characters.
Search Modes
Next, we’re going to take a look at the sixth and final argument of the XLOOKUP function, the Search Mode. This argument specifies the direction of the search that Excel will perform for the Lookup Value. There are four Search Modes:
The last two search modes are not used very often. In the past, binary searches were faster than non-binary searches, but using this mode meant that your data had to be sorted in ascending or descending order by the data within the Lookup Array. Improvements have been made to Excel, and binary searches are no longer faster than their non-binary counterparts.
First to Last
If you do not specify the Search Mode argument within your XLOOKUP function, Excel automatically searches from the first item in the Lookup Array to the last item in the Lookup Array to try to find a match. The returned result is based upon the first match the function finds.
You can also explicitly specify that you want to use the first to last Search Mode by passing a “1” as the sixth argument of your XLOOKUP function, as seen in this example:
If you look back at the sorted list of Avg. High temperatures, you’ll see that there are three temperatures of “90”. The first month with an Avg. High of 90 is June, so that is returned as the result of this formula.
Last to First
As you probably already guessed, the Last to First Search Mode searches for the Lookup Value in your Lookup Array, starting with the last item in the array and working backward to the first item until it finds a match.
In this example, we’ll do the same search as the previous example, but with the Search Mode set to Last to First, we’ll get a different result:
This formula returned “August” because the search started with the last item in our Lookup Array. August was the first item to have an Avg. High temperature of 90, so that was returned as the result.
Other Function Details
Now that we’ve looked at all the arguments within the XLOOKUP function, let’s look at a couple of other things you should be aware of when using the XLOOKUP function.
Skipping Arguments
When you use the XLOOKUP function or any Excel function for that matter, you can skip optional arguments. You always need to include all required arguments, but if there are multiple optional arguments, you may exclude some or all of them.
Optional arguments always come after required arguments in function definitions. So, if you want to exclude all the optional arguments of a function, just leave them off the function.
However, if you want to skip some optional arguments and specify others, you simply type the necessary commas and leave the argument you don’t want to specify empty. If the argument or arguments you want to skip are at the end, you do not need to include the commas.
Here are some examples to show you how to skip optional arguments in the XLOOKUP function:
To skip the “If Not Found” argument, use the syntax in Figure 23:
The XLOOKUP function has two commas after the Return Array (B4:B15). This indicates that the If Not Found argument was not specified, but the Match Mode and Search Mode arguments were.
In Figure 24, the Match Mode was skipped. Notice the two commas after the If Not Found argument.
In Figure 25, the Search Mode argument was not included. The last argument specified was the Match Mode (0). The Search Mode will default to “1” (first to last search). The additional comma at the end is not necessary.
In the final example in Figure 26, you’ll notice three commas in a row after the Return Array. These indicate that the If Not Found and Match Mode arguments were not specified, but the Search Mode was.
Reusing an XLOOKUP Formula
In all of our examples so far, we have used the XLOOKUP formula in one cell to return one piece of information. But what if you wanted to return several pieces of information from the data source? We can easily do this by copying the XLOOKUP formula from one cell and pasting it into another cell.
When you copy and paste formulas, you need to use the appropriate cell reference types. If you want the cell references to change relative to their old location, use relative cell references (no dollar signs in the cell references). If you do not want the cell references to change when you copy them to a new cell, you need to make them absolute by using dollar signs in the cell references.
As an example, let’s say that I wanted to return the Avg. High, Avg. Low, and Avg. Precip. for a specific month to three cells within my worksheet. To do this, I would write the first XLOOKUP function to return the Avg. High. I would then copy and paste the formulas to two other cells to return the Avg. Low and Avg. Precip. values.
In Figure 27:
- The Lookup Value is specified in cell G3.
- Cells G4 through I4 display labels for the values in the next row.
- Cells G5 through I5 are the results of the three XLOOKUP functions.
- Cells G8 through G10 use the FORMULATEXT function to display the XLOOKUP functions used in cells G5 through I5.
In all three of the XLOOKUP functions in Figure 27, the Lookup Value and Lookup Array are both set to absolute cell references by placing dollar signs before the column and row indicators. Making the references absolute ensures that those references do not change wherever they are copied. However, the Return Array does not use an absolute reference (no dollar signs). When we copy the XLOOKUP function from cell G5 to G6, the Return Array changed from B4:B15 (Avg. High temps) to C4:C15 (Avg. Low temps). This is precisely what we wanted to happen. When we paste the formula in cell I5, the Return Array changes to D4:D15, which will return the Avg. Precip.
Returning a Spilled Array
While copying and pasting the formulas in the previous example worked well, there’s an even faster way to accomplish the same result by using what’s called a spilled array. When you write a formula that returns an array (multiple answers) as the result, the values will be displayed in additional cells to the right or down the worksheet.
In our case, we can use a spilled array to return the values for the three returned values we’re looking for related to our climate data. Here’s what the data would look like:
In this case, we entered the XLOOKUP function in cell G14; you can see the formula in cell G17. Multiple answers will be returned from this formula because the Return Array encompasses three columns of data (B4:D14). When I hit enter to save this formula and see the results, the answers populated in cells G14, H14, and I14. In Figure 28, it’s a little hard to see, but there is a blue border around cells G14 through I14; this indicates a spilled array.
Looking at the formulas in cells G18 and G19, you’ll see an #N/A error. This is because there is no formula in cells H14 and I14.
This is a quick example of a spilled array, but basically, if more than one answer is the returned result from a formula, all the answers will be displayed in additional cells in your worksheet.
Conclusion
So that’s the XLOOKUP function. There’s a lot more you can do with the function, but I hope you see how useful this function is and that it’s a great alternative to the other lookup functions available in Excel.
