Excel 2024: Twelve Benefits of XLOOKUP

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

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.

Thanks to a suggestion from Rico S. 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".

---

---

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.

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

By default, XLOOKUP looks for an exact match. Using the 5th argument for match_mode allows you to look for the exact value or just smaller or the exact value or just larger.

Before XLOOKUP, VLOOKUP was able to do the just smaller. For just larger, you had to use MATCH. But both of those obsolete functions required the lookup table to be sorted. With XLOOKUP, the table does not have to be sorted.

XLOOKUP's optional fifth argument match_mode uses these values:

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

Caution: The 1 and -1 here are opposite of MATCH. These make more sense than MATCH.

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

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 Benefit 6: Sideways XLOOKUP replaces HLOOKUP

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

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.

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

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.

XLOOKUP Benefit 10: One Formula Performs All XLOOKUP

A single formula can perform an entire column of XLOOKUP. In the figure below, one formula in C4 replaces 11 XLOOKUP formulas. You can not combine benefit #9 and #10 to return many rows of many columns because Excel won't currently return an array of arrays..

Benefit 11: XLOOKUP 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. XLOOKUP is the eighth 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.

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.

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.

Press Evaluate two more times, and the interim formula will be =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), or a Comma (Union operator).

XLOOKUP Benefit 12: 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 Benefit #9, 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.

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.