One of the most commonly used and confusing Excel functions is much better in Excel 365 and Excel 2021. Vlookup() has a new, better and easier replacement Xlookup(). It’s a truly good and useful addition to Excel, some might call it a godsend.
Xlookup is available in Excel 365 for Windows/Mac/Apple/Android plus Excel 2021 for Windows/Mac. It’s a big deal and worth an extensive look. We’ll focus on Xlookup here, there’s also Xmatch() to improve upon Match().
Xlookup() is a simpler way to find a value by searching (lookup) a range or table with more options and less restrictions than Vlookup() or HLookup()
Benefits of Xlookup
Bi-directional – vertical or horizontal lookups in the one function. Just select the lookup range and Xlookup will figure out the direction.
Results from left or right – Vlookup() could only return results from columns to the right of the lookup column. Xlookup works to a specific column/row that’s left/right/above or below.
Specific column/row – Xlookup lets you choose a specific column/row to return. That might not seem like a big deal but anyone who has suffered with Vlookup/Hlookup will be singing hallelujahs.
Vlookup() result must be from a column relative to the right of the lookup column. If the result column was left (like the Date column above), it wasn’t possible without rearranging the table or considerable formula gymnastics. Even worse, Vlookup/Hlookup worked with relative positioning. Adding or deleting a column/row could mess up the entire function.
Exact matching – Xlookup defaults to an exact match which is what most people expect. Vlookup() defaults to a ‘near match’ which drove many people crazy.
Better Matching – a specific match mode parameter gives more control. Matches can be exact, nearest lower, nearest higher or wildcard.
Xlookup can find the next largest result or last occurrence both not possible with Vlookup/Hlookup.
Better searches – search ‘first to last’ or vice-versa. Excel smarties can try a binary sorted option – ascending or descending.
Xlookup in theory
The full syntax of Xlookup()
XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
lookup_value
The lookup value
lookup_array
The array or range to search
return_array
The array or range to return
match_mode
Specify the match type:
0 – Exact match. If none found, return #N/A. This is the default.
-1 – Exact match. If none found, return the next smaller item.
1 – Exact match. If none found, return the next larger item.
2 – A wildcard match where *, ?, and ~ have special meaning.
Wildcard options
? (question mark) Any single character
* (asterisk) Any number of characters
~ (tilde) the ‘escape’ character if you need ?, *, or ~ as standard characters, not search terms. E.g. ~? for a question mark.
search_mode
Specify the search mode to use:
1 – Perform a search starting at the first item. This is the default.
-1 – Perform a reverse search starting at the last item.
2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Xlookup in practice
Here’s an example of Xlookup() in action doing something that Vlookup could not.
We’ll lookup a name from Col B and get the date beside it in Col A.
=XLOOKUP(E2,Dwarves[Name],Dwarves[Date],0,1)
E2 – the name to lookup
Dwarves[Name] – where to look for a match
Dwarves[Date] – the matching value to return.
That might seem simple but anyone who knows VLookup() will tell you it’s not possible. Vlookup() only returns values to the right of the lookup column.
Xlookup() doesn’t care where the matching column or row is.
We’ve included the last two parameters in this example, even though they are the defaults.
0 – look for an exact match.
1 – search from first to last down the list.
Vlookup and Hlookup are still there
Vlookup() and HLookup() aren’t disappearing from Excel. Both functions are so widely used they won’t be removed from Excel.
Microsoft says they ‘strongly recommend using XLOOKUP in favor of VLOOKUP and HLOOKUP’. While that might work in limited situations, it’s not really practical. Even when Xlookup is publicly available in Excel 365 for Windows and Mac, it’s not available in past Excel’s including Excel 2019.
For maximum compatibility, Vlookup and Hlookup are still the best choices for now. Especially if there’s a chance the worksheet will be opened in an older Excel.
Over time, Xlookup() will become more widely used and will be practical in shared workbooks.
