The XLOOKUP function in Excel is a newer function, introduced in Excel 365. It is similar to VLOOKUP and HLOOKUP, but with greater simplicity and flexibility. For example, while VLOOKUP can only search for values to the right of the lookup column, XLOOKUP can search left or right, and with fewer arguments. Additionally, while XLOOKUP only has three required arguments, there are additional optional arguments which you can use to tailor the XLOOKUP function to your needs.
As noted above, XLOOKUP has three required arguments (function inputs) and three optional arguments. These are explained below.
=XLOOKUP(lookup_value, lookup_range, return_range, [not_found], [match_mode], [search_mode])
The following example of the XLOOKUP function is for a vertical search, similar to what VLOOKUP is traditionally used for. Suppose you have the following Excel table and you want to know how many scenes Dwight appeared in.
A | B | C | D | |
---|---|---|---|---|
1 | Character | Episodes | Scenes | |
2 | Michael | 137 | 3033 | |
3 | Dwight | 186 | 2367 | |
4 | Jim | 185 | 2150 | |
5 | Pam | 182 | 1922 | |
6 | Andy | 144 | 1341 |
You would use the following XLOOKUP formula:
=XLOOKUP("Dwight", A:A, C:C)
Or
=XLOOKUP("Dwight", A2:A6, C2:C6)
This formula would return 2367 because this is the cell in the return range that corresponds with (same position as) the lookup value in the lookup range. Remember that "Dwight" is in quotes because text values in Excel must be enclosed in quotes.
Recall that unlike the VLOOKUP function in Excel, the XLOOKUP function can search for values from right to left. Using the same Excel table as the example above, say you know that one of the characters appeared in 185 episodes, and you want to find out which character it is.
=XLOOKUP(185, B:B, A:A)
This formula returns "Jim" because the cell that contains "Jim" in the return range (A:A) is in the same position as the cell that contains 185 in the lookup range (B:B). It doesn't matter if you're searching left to right or right to left.
The XLOOKUP function in Excel can also be used for horizontal searches, similar to what HLOOKUP has traditionally been used for. Take the following Excel table and suppose you want to know the birth city of Lil Wayne.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Snoop Dogg | Tupac | Jay-Z | Lil Wayne | |
2 | First Name | Calvin | Tupac | Shawn | Dwayne |
3 | Birth Year | 1971 | 1971 | 1969 | 1982 |
4 | Birth City | Long Beach | New York | New York | New Orleans |
You would use the following XLOOKUP formula:
=XLOOKUP("Lil Wayne", 1:1, 4:4)
Or
=XLOOKUP("Lil Wayne", B1:E1, B4:E4)
The formula would search row 1 for the value "Lil Wayne", then would search for the corresponding value in row 4 to return "New Orleans".
Conversely, unlike HLOOKUP, the XLOOKUP function can search from bottom to top. For example, say you wanted to know which rapper was born in 1969. You could use the following formula:
=XLOOKUP(1969, 3:3, 1:1)
Or
=XLOOKUP(1969, B3:E3, B1:E1)
These formulae would search row 3 for the value 1969 and return the value in the corresponding cell of row 1, which is "Jay-Z".
Here are some examples of errors you may get when using the XLOOKUP function in Excel, as well as tips on how to resolve them.
The XLOOKUP function returns the #VALUE error if the lookup range and return range are incompatible. Oftentimes this is because the two ranges are different sizes or shapes. For example, take our table of Office characters again.
A | B | C | D | |
---|---|---|---|---|
1 | Character | Episodes | Scenes | |
2 | Michael | 137 | 3033 | |
3 | Dwight | 186 | 2367 | |
4 | Jim | 185 | 2150 | |
5 | Pam | 182 | 1922 | |
6 | Andy | 144 | 1341 |
Say we look up Pam's episode count with the following formula:
=XLOOKUP("Pam", A1:A6, B:B)
This formula would return the #VALUE error because the range A1:A6 is a different size than the range B:B. To resolve this error, we would ensure that both ranges are the same size and dimensions:
=XLOOKUP("Pam", A1:A6, B1:B6)
The XLOOKUP function in Excel returns the #N/A error if it cannot find the lookup value in the lookup range. For example, say you want XLOOKUP to return a character who has appeared in 184 episodes.
A | B | C | D | |
---|---|---|---|---|
1 | Character | Episodes | Scenes | |
2 | Michael | 137 | 3033 | |
3 | Dwight | 186 | 2367 | |
4 | Jim | 185 | 2150 | |
5 | Pam | 182 | 1922 | |
6 | Andy | 144 | 1341 |
You use the following formula:
=XLOOKUP(184, B:B, A:A)
This formula returns the #N/A error because the lookup value (184) does not exist in the lookup range (B:B).
One way to mitigate the #N/A error is to supply a not_found argument. This is the fourth argument in the XLOOKUP function and is optional, but tells the function to return a specified value if the lookup value is not located n the lookup range.
=XLOOKUP(185, B:B, A:A, "Character not found")
This formula would return "Jim" because 185 exists in the lookup range.
=XLOOKUP(184, B:B, A:A, "Character not found")
This formula would return "Character not found" because 184 does not exist in the lookup range.
Another way to resolve the #N/A error is to specify a match mode argument as described below.
The XLOOKUP function in Excel allows an optional match mode argument, which is the fifth argument in the XLOOKUP function. Match mode tells Excel what to do if XLOOKUP cannot find the lookup value in the lookup range. Here are the acceptable values for match_mode:
For example, take our Excel table below and say we want to return the character who appeared in 184 scenes.
A | B | C | D | |
---|---|---|---|---|
1 | Character | Episodes | Scenes | |
2 | Michael | 137 | 3033 | |
3 | Dwight | 186 | 2367 | |
4 | Jim | 185 | 2150 | |
5 | Pam | 182 | 1922 | |
6 | Andy | 144 | 1341 |
=XLOOKUP(184, B:B, A:A)
If the match mode argument is omitted or is 0, then XLOOKUP would return #N/A because 184 does not exist in the lookup range.
=XLOOKUP(184, B:B, A:A, "Character not found")
This formula would return "Character not found" because 184 does not exist in the lookup range, but we have supplied the not_found argument.
=XLOOKUP(184, B:B, A:A, "Character not found", 1)
Here we pass 1 to the match_mode argument of XLOOKUP, so it will match on the next highest value because 184 doesn't exist. The next highest value in B:B is 185, so XLOOKUP would return "Jim".
=XLOOKUP(184, B:B, A:A, "Character not found", -1)
Here we pass -1 to the XLOOKUP function, so it will match on the next lowest value in B:B, which is 182. In this case, XLOOKUP would return "Pam".
The XLOOKUP function in Excel also supports wildcard matching when match_mode is set to 2. When wildcard matching in Excel, an asterisk (*) replaces any number of characters, whereas a question mark (?) replaces exactly one character.
=XLOOKUP("M*", A:A, C:C, "Character not found", 2)
This formula would return 3033 because "M*" would match with "Micheal" and return the corresponding value in the range C:C.
The XLOOKUP function in Excel offers a sixth and final argument called search_mode, which is optional. The search_mode argument in Excel tells XLOOKUP whether to search from top to bottom or from bottom to top (or left to right vs. right to left in the case of horizontal lookups). Here is a list of acceptable search_mode values:
For example, say you had the table of rappers below and you wanted to find the rapper born in 1971. If you look closely, you'll notice that two rappers were born in 1971.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Snoop Dogg | Tupac | Jay-Z | Lil Wayne | |
2 | First Name | Calvin | Tupac | Shawn | Dwayne |
3 | Birth Year | 1971 | 1971 | 1969 | 1982 |
4 | Birth City | Long Beach | New York | New York | New Orleans |
=XLOOKUP(1971, 3:3, 1:1, "Rapper not found", 0)
In this case, search_mode is omitted form the formula, so the formula would return "Snoop Dogg" because XLOOKUP starts searching at the first item by default. The first cell which contains 1971 is B3, so the result is the corresponding cell in the return range, which is B1.
=XLOOKUP(1971, 3:3, 1:1, "Rapper not found", 0, 1)
This formula yields the same result because a search_mode value of 1 tells XLOOKUP to start with the first value, which is the same as the default behavior.
=XLOOKUP(1971, 3:3, 1:1, "Rapper not found", 0, -1)
This formula has a search_mode value of -1, which tells XLOOKUP to search in reverse, starting with the last item. In this case, XLOOKUP searches row 3:3 from right to left for the value 1971. In this case, the first match is in cell C3, so XLOOKUP would return "Tupac".