Excel 2020: Twelve Benefits of XLOOKUP

August 26, 2020 - by Bill Jelen

The new XLOOKUP function is rolling out to Office 365 starting in November 2019. Joe McDaid of the Excel team designed XLOOKUP to unify the people who use VLOOKUP and the people who use INDEX/MATCH. This section will discuss the 12 benefits of XLOOKUP:

Exact match is the default.
Integer-based third argument of VLOOKUP is now a proper reference.
IFNA is built-in to handle missing values.
XLOOKUP has no problem going to the left.
Find next-smaller or next-larger match without sorting the table.
XLOOKUP can do HLOOKUP.
Find the last match by searching from the bottom.
Wildcards are "off" by default, but you can turn them back on.
Return all 12 months in a single formula.
Can return a cell reference if the XLOOKUP is next to a colon such as XLOOKUP();XLOOKUP()
Can do a two-way match like INDEX(,MATCH,MATCH) can do.
Can sum all lookups in a single formula like LOOKUP could do.

Here is the syntax: =XLOOKUP(Lookup_Value, Lookup_Array, Results_Array, [if_not_found], {match_mode], [search_mode]).

XLOOKUP Benefit 1: Exact Match by Default

99% of my VLOOKUP formulas end in ,FALSE or ,0 to indicate an exact match. If you always use the exact match version of VLOOKUP, you can start leaving the match_mode off of your XLOOKUP function.

In the following figure, you are looking up W25-6 from cell A4. You want to look for that item in L8:L35. When it is found, you want the corresponding price from column N. There is no need to specify False as the match_mode because XLOOKUP defaults to an exact match.

XLOOKUP the value in A4. Look in L8:L35. Return the corresponding price from N8:N35.

XLOOKUP Benefit 2: the Results_Array is a reference instead of an integer

Think about the VLOOKUP formula that you would use before XLOOKUP. The third argument would've been a 3 to indicate that you wanted to return the 3rd column. There was always a danger that a clueless co-worker would've inserted (or deleted) a column in your table. With an extra column in the table, the VLOOKUP that had been returning a price would start returning a description. Because XLOOKUP was pointing to a cell reference, the formula rewrites itself to keep pointing to the price that is now in column O.

The old VLOOKUP would fail if someone inserted a new column in the lookup table. XLOOKUP keeps working.

XLOOKUP Benefit 3: IFNA is built in as an optional argument

The dreaded #N/A error is returned when your lookup value is not found in the table. In the past, to replace #N/A with something else, you would have to use IFERROR or IFNA wrapped around the VLOOKUP.

When an item is not found, it returns #N/A from VLOOKUP or XLOOKUP...

Thanks to a suggestion from Rico on my YouTube channel, the Excel team incorporated an optional fourth argument for if_not_found. If you want to replace those #N/A errors with zero, simply add ,0 as the fourth argument. Or, you could use some text, such as "Value not found".

The optional fourth argument in XLOOKUP is "if not found". Put a 0 or "Not found" there.

XLOOKUP Benefit 4: No problem looking to the left of the key field

VLOOKUP can not look to the left of the key field without resorting to VLOOKUP(A4, CHOOSE({1,2}, G7:G34,F7:F34),2,False). With XLOOKUP, there is no problem having the Results_array to the left of the Lookup_array.

With XLOOKUP, there is no problem returning the Category from column F while looking up part numbers in column G. This was always VLOOKUP's weakness: it could not look to the left.

XLOOKUP Benefit 5: Next-smaller or next-larger match without sorting

VLOOKUP had an option to look for the exact match or just smaller value. You could either leave the fourth argument out of VLOOKUP, or change the False to True. For this to work, the lookup table had to be sorted in ascending sequence.

An example of the Approximate Match version of VLOOKUP. Any sale from 10 thousand to 20 thousand gets a bonus of $12.

But VLOOKUP did not have the ability to return the exact match or next larger item. For that, you had to switch over to using MATCH with a -1 as the match_mode and you had to be careful that the lookup table was sorted descending.

XLOOKUP's optional fifth argument match_mode can look for only the exact match, equal to or just smaller, equal to or just larger. Note that the values in XLOOKUP make more sense than in MATCH:

-1 finds the value equal to or just smaller
0 find an exact match
1 finds the value equal to or just larger.

But, the most amazing part: the lookup table does not have to be sorted and any match_mode will work.

Below, a match_mode of -1 find the next-smaller item.

XLOOKUP's fifth argument is Match_Mode. 0 is for exact match. Negative one is used for Exact Match or Next Smaller Item. Positive 1 is for exact match or next larger item. 2 is for Wildcard Match. To mirror what VLOOKUP with True in the fourth argument would do, put a negative one as the match_mode argument in XLOOKUP.

Here, a match_mode of 1, finds what vehicle is needed depending on the number of people in the party. Note that the lookup table is not sorted by passengers and the vehicle name is to the left of the key.

XLOOKUP can do something VLOOKUP could not do: find the exact match or just larger. In this case, a tour company has a list of reservations. Based on the number of passengers, the lookup table shows what vehicle you need for those people.

The table says:

Bus holds 64 people
Car holds 4 people
Motorcyle holds 1 person
Tour Van holds 12 people
Van hold 6 people.

As a bonus, the data is sorted by Vehicle (in the old solution, using MATCH, the table would have to be sorted descending by Capacity. Also: the Vehicle is to the left of Capacity.

XLOOKUP Benefit 6: Sideways XLOOKUP replaces HLOOKUP

The lookup_array and results_array can be horizontal with XLOOKUP, making it simple to replace HLOOKUP.

Here the lookup table is horizontal. In the past, this would require HLOOKUP, but XLOOKUP can deal with a table that goes sideways.

XLOOKUP Benefit 7: Search from the bottom for latest match

I have an old video on YouTube answering a question from a British horse farm. They had a fleet of vehicles. Every time a vehicle came in for fuel or service, they logged vehicle, date, and mileage in a spreadsheet. They wanted to find the latest known mileage for each vehicle. While the Excel-2017 era MAXIFS might solve this today, the solution many years ago was an arcane formula using LOOKUP and involved division by zero.

Today, XLOOKUP's optional sixth argument lets you specify that the search should start from the bottom of the data set.

Note

While this is a great improvement, it only lets you find the first or last match. Some people hoped this would let you find the second or third match, but that is not the intention of the search_mode argument.

Caution

The figure above shows that there are search modes using the old binary search. Joe McDaid advises against using these. First, the improved lookup algorithm from 2018 is fast enough that there is no significant speed benefit. Second, you run the risk of a clueless co-worker sorting the lookup table and introducing wrong answers.

XLOOKUP Benefit 8: Wildcards are "turned off" by default

Most people did not realize that VLOOKUP is treating asterisk, question mark, and tilde as wildcard characters as described in "#51 Use a Wildcard in VLOOKUP" on page 143. With XLOOKUP, wildcards are turned off by default. If you want XLOOKUP to treat these characters as a wildcard, use 2 as the Match_Mode.

Very few people realized that VLOOKUP is treating asterisks in the lookup value as a wildcard. By default, XLOOKUP is not using wildcards, but you can force it to behave like VLOOKUP if you use a Match Mode of 2: Wildcard Character Match.

XLOOKUP Benefit 9: Return All 12 Months in a Single Formula!

This is really a benefit of Dynamic Arrays, but it is my favorite reason to love XLOOKUP. When you have to return all 12 months in a lookup, a single formula entered in B6 with a rectangular return_array will return multiple results. Those results will spill into adjacent cells.

In the figure below, a single formula entered in B7 returns all 12 answers shown in B7:M7.

A single XLOOKUP in the January column returns numbers for January through December. This is done by specifying a results_array with 12 columns.

XLOOKUP Benefit 10: Can return a Cell Reference If Adjacent to Colon

This one is complex but beautiful. In the past, there were seven functions that would change from returning a cell value to returning a cell reference if the function was touching a colon. For an example, see Use A2:INDEX() as a Non-Volatile OFFSET. XLOOKUP is the eights function to offer this behavior, joining CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET, and SWITCH.

Consider the following figure. Someone select Cherry in E4 and Fig in E5. You want a formula that will sum everything from B6 to B9.

The figure shows two XLOOKUP formulas in two cells. The first returns 15 from cell B6. The second retruns 30 from B9. But then in a third cell, there is a formula that joins the two XLOOKUP formulas with a colon and then wraps that in a SUM function. The result is the SUM of B6:B9, because XLOOKUP can return a cell reference if the function appears next to an operator such as a colon. To prove that this is working, the next several figures will show this formula in the Evaluate Formula dialog.

In the figure above, you can see that an XLOOKUP of E4 will return the 15 from cell B6. An XLOOKUP of E5 will return the 30 from B9. However, if you take the two XLOOKUP functions from cells D9 and D10 and put them together with a colon in-between, the behavior of XLOOKUP changes. Instead of returning 15, the first XLOOKUP returns the cell address B6!

To prove this, I've selected D7 and use Formulas, Evaluate Formula. After pressing Evaluate two times, the next part to be calculated is XLOOKUP("Cherry",A4:A29,B4:B29), as shown here.

This shows the Evaluate Formula dialog just before evaluating the first XLOOKUP. This XLOOKUP appears just before a colon.

Press Evaluate again and amazingly, the XLOOKUP formula returns $B$6 instead of the 15 stored in B6. This happens because there is a colon immediately following this XLOOKUP formula.

Click Evaluate and the first XLOOKUP returns $B$6 instead of 15.

Press Evaluate two more times, and the interim formula will be =SUM(B6:B9).

After evaluating the second XLOOKUP, the interim formula is =SUM(B6:B9).

This is amazing behavior that most people don't know about. Excel MVP Charles Williams tells me that it can be triggered with any of these three operators next to XLOOKUP:

Colon
Space (Intersection operator)
Comma (Union operator)

XLOOKUP Benefit 11: Two-way match like INDEX(,MATCH,MATCH)

For all of my VLOOKUP friends, the INDEX/MATCH people have been waiting to see if XLOOKUP can handle a two-way match. The great news: it can do it. The bad news: the methodology is a little different than the INDEX/MATCH fans would expect. It might be a little over their heads. But I am sure they can come around to this method.

For a two-way match, you want to find which row contains the account number A621 shown in J3. So, the XLOOKUP starts out easy enough: =XLOOKUP(J3,A5:A15. But then you have to provide a results_array. You can use the same trick as in XLOOKUP Benefit 9: Return All 12 Months in a Single Formula above, but use it to return a vertical vector. An inner XLOOKUP looks for the J4 month in the month headings in B4:G4. The return_array is specified as B5:G15. The result is that the inner XLOOKUP returns an array like the one shown in I10:I20 below. Since A621 is found in the fifth cell of the lookup_array and 104 is found in the fifth cell of the results_array, you get the correct answer from the formula. Below, J6 shows the old way. J7 returns the new way.

XLookup J3 in the list of accounts in A5:A15. For the Results Array, use XLOOKUP(J4,B4:G4,B5:G15). In this formula, B4:G4 is a list of months. B5:G15 is the rectangular array of values for all accounts for all months. In another cell, just the inner XLOOKUP shows how it returns the entire column of values for May.

XLOOKUP Benefit 12: Sum all lookup values in a single formula

The ancient LOOKUP function offered two strange tricks. First, if you are trying to figure out the total amount of bonus expense to accrue, you could ask LOOKUP to lookup all of the values in a single formula. In the image below, LOOKUP(C4:C14 is doing 11 lookups. But the LOOKUP function did not offer an exact match and required the lookup table to be sorted.

Lookup 13 values and sum them. This used to work with LOOKUP, but it also works with XLOOKUP. Specify all of the lookup values C4:C14 as the first argument. Wrap the XLOOKUP in a SUM function.

With XLOOKUP, you can specify a range as the lookup_value and XLOOKUP will return all of the answers. The benefit is that XLOOKUP can do exact matches.

The trick of using LOOKUP to sum all of the lookup results only worked with the approximate match version of Lookup. Here, XLOOKUP is doing an exact match on all of the names in L4:L14 and getting a total of all of the results.

Bonus Tip: What about a Twisted LOOKUP?

Excel MVP Mike Girvin often shows a trick of the LOOKUP function where the Lookup_Vector is vertical and the Result_Vector is horizontal. XLOOKUP will not natively support this trick. But, if you cheat a little bit and wrap the results_array in the TRANSPOSE function, you can manage a twisted lookup.

Here the lookup array is vertical and the results array is horizontal. The old LOOKUP function can handle this,, but to do it with XLOOKUP, you have to wrap either array in TRANSPOSE.