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 VLOOKUPyou can start leaving the match_mode off of your XLOOKUP function.

In the following figureyou are looking up W25-6 from cell A4. You want to look for that item in L8:L35. When it is foundyou 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 tablethe VLOOKUP that had been returning a price would start returning a description. Because XLOOKUP was pointing to a cell referencethe 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 pastto replace #N/A with something elseyou would have to use IFERROR or IFNA wrapped around the VLOOKUP.

Thanks to a suggestion from Rico S. on my YouTube channelthe Excel team incorporated an optional fourth argument for if_not_found. If you want to replace those #N/A errors with zerosimply add ,0 as the fourth argument. Oryou could use some textsuch 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(A4CHOOSE({1,2}G7:G34,F7:F34),2,False). With XLOOKUPthere 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 defaultXLOOKUP 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 XLOOKUPVLOOKUP was able to do the just smaller. For just largeryou had to use MATCH. But both of those obsolete functions required the lookup table to be sorted. With XLOOKUPthe 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.

Belowa match_mode of -1 find the next-smaller item.

Herea match_mode of 1finds 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 XLOOKUPmaking 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 servicethey logged vehicledateand mileage in a spreadsheet. They wanted to find the latest known mileage for each vehicle. While the Excel-2017 era MAXIFS might solve this todaythe solution many years ago was an arcane formula using LOOKUP and involved division by zero.

TodayXLOOKUP'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. Firstthe improved lookup algorithm from 2018 is fast enough that there is no significant speed benefit. Secondyou 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 asteriskquestion markand tilde as wildcard characters. With XLOOKUPwildcards are turned off by default. If you want XLOOKUP to treat these characters as a wildcarduse 2 as the Match_Mode.

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

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

In the figure belowa 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 belowone 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 pastthere 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 behaviorjoining CHOOSEIFIFSINDEXINDIRECTOFFSETand 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 aboveyou can see that an XLOOKUP of E4 will return the 15 from cell B6. An XLOOKUP of E5 will return the 30 from B9. Howeverif you take the two XLOOKUP functions from cells D9 and D10 and put them together with a colon in-betweenthe behavior of XLOOKUP changes. Instead of returning 15the first XLOOKUP returns the cell address B6!

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

Press Evaluate again and amazinglythe 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 timesand 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: ColonSpace (Intersection operator)or a Comma (Union operator).

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

For all of my VLOOKUP friendsthe 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 matchyou want to find which row contains the account number A621 shown in J3. Sothe 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 #9but 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_arrayyou get the correct answer from the formula. BelowJ6 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. Butif you cheat a little bit and wrap the results_array in the TRANSPOSE functionyou can manage a twisted lookup.