The purpose of lookup functions in Microsoft Excel is to look for a value (usually through a myriad of columns and rows) and to return some information about that value quickly (similar to “go fetch”!). I’m sure you have heard of or may be using some of the most popular lookup functions in Excel such as VLOOKUP or HLOOKUP. While these legacy lookup functions are still available in Excel, now through Office 365 Microsoft has added a new superior alternative to looking up and retrieving data with the XLOOKUP function. Many users will find XLOOKUP to be more straightforward and intuitive. Most will also find XLOOKUP to be even more powerful!
Some of the critical differences (and advantages) between XLOOKUP and other lookup functions include:
- XLOOKUP defaults to an exact match, whereas VLOOKUP and HLOOKUP default to an approximate match.
- With XLOOKUP you do not have to specify a column index number as you do with VLOOKUP or a row index number as you do with HLOOKUP.
- The arrangement of columns and rows does not matter with XLOOKUP. This is because the function can look to the left or right when using it as an alternative to VLOOKUP. Likewise, it can look above or below when using it as an alternative to HLOOKUP.
- XLOOKUP allows you to specify what happens if your lookup value isn’t found, without having to include an IFERROR function.
The XLOOKUP function resides on the Insert ribbon in the Function Library category “Lookup & Reference”.
See it in action below:
