XLOOKUP and XMATCH – welcomed simplifications
VLOOKUP is commonly used for fetching data in lists. In quite a few situations, you rather need INDEX and MATCH to get the needed functionality.
XLOOKUP will get the best of both possibilities.
In August 2019, Microsoft began slowly releasing the XLOOKUP function to some Office 365 Insiders. The main benefits of XLOOKUP:
- Can find the last match!
- Can look to the left!
- Defaults to an exact match (unlike VLOOKUP which defaults to True for the 4th argument)
- Defaults to not support wildcards, but you can explicitly allow wildcards if you want them
- Has all the speed improvements released to VLOOKUP in 2018
- No longer relies on Column Number, so it won’t break if someone inserts a column in the middle of the lookup table
- Performance improvement because you are only specifying two columns instead of the whole lookup table
- XLOOKUP returns a range instead of VLOOKUP returning a value
There will also be a similar update of MATCH, XMATCH.