XLOOKUP Function in Excel

Syntax of the XLOOKUP Function

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])

1. lookup_value (required): This is the value that you give XLOOKUP which it will use to find the position of the corresponding value you want it to return.
2. lookup_range (required): This is the range of cells where the lookup_value lives.
3. return_range (required): This is the range of cells which contains the value that you want XLOOKUP to return.
4. not_found (optional): XLOOKUP will return this value if a valid match to the lookup_value is not found. By default, XLOOKUP will return #N/A when this happens.
5. match_mode (optional): This argument specifies what to do if Excel does not find an exact match to the lookup_value: it can look for the next highest or next lowest value. Additionally, you can use wildcard matching with XLOOKUP when specified here.
6. search_mode (optional): This argument specifies whether XLOOKUP should search from left to right (top to bottom) or right to left (bottom to top).

Example Using XLOOKUP in Excel: Vertical Search

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.

Using XLOOKUP to Search From Right to Left

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.

Example Using XLOOKUP in Excel: Horizontal Search

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".

XLOOKUP Errors in Excel

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.

#VALUE Error from XLOOKUP

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)

#N/A Error from XLOOKUP

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.

Match Mode For XLOOKUP

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:

• 0 (or omitted): return N/A (or not_found argument) if lookup value not found
• 1: match with next highest value if lookup value not found
• -1: match with next lowest value if lookup value not found
• 2: wildcard match

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.

Search Mode For XLOOKUP

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:

• 1: start search at the first item. This is the default and tells XLOOKUP to search top (or left) to bottom (or right).
• -1: start search at the last item in reverse order. Tells XLOOKUP to search from bottom (right) to top (left).
• 2: perform binary search when sorted in ascending order (not covered in this tutorial).
• -2: perform binary search when sorted in descending order (not covered in this tutorial).

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".