XLOOKUP a new function recently launched with Microsoft Office 365 will be replacing the widely used VLOOKUP, HLOOKUP and INDEX/MATCH functions to run searches in a table of Excel data.
XLOOKUP and VLOOKUP functions in Excel are similar in terms of use to find or ‘lookup’, a value from a table and list and then return a related result. But there are many things which keep them different from each other. Excel users always think that which one is a better function to use. The primary one is, XLOOKUP not supported by older versions of Excel. ( XLOOKUP Overview)
How XLOOKUP different from VLOOKUP and HLOOKUP –
- XLOOKUP has Exact match mode by default but VLOOKUP doesn’t..
- VLOOKUP works from left to right, if there any value to lookup from right to left it will not work. XLOOKUP doesn’t have this limitation.
- lookup_array and retun_array are two separate arguments that are similar to how INDEX MATCH formulas work. But with the VLOOKUP you just have the single table_array that contains both the column to look in and the return column.
- HLOOKUP needs a separate function for horizontal lookup but XLOOKUP can perform horizontal lookup by referencing rows instead of columns for the lookup and return arrays.
- VLOOKUP formulas are not able to handle column insertions or deletions. You just need to adjust the column index number whenever you try to insert or delete column.
- XLOOKUP can perform searches from smallest to largest or from largest to smallest but VLOOKUP sort smallest to largest.
- VLOOKUP required you to input an entire data set, but XLOOKUP only requires you to reference the relevant columns or rows. This way it increases the calculation speed of the spreadsheet.
- XLOOKUP returns more than one value.
- XLOOKUP search from bottom to top but VLOOKUP doesn’t.
- You can set custom message if lookup value not found with XLOOKUP.
