Yesterday witnessed the beginning of a new dawn in world of spreadsheet occasioned by the arrival of an elegant, sophisticated and user-friendly Excel function named XLOOKUP. The new XLOOKUP function provides a strong alternative to the VLOOKUP function which has been around since 1985 when Excel 1 for Macintosh was released.
Traditionally, the Excel VLOOKUP, among other limitations, is unable to lookup value to the left; having to provide 0 or FALSE to perform Exact match; do not allow users to either insert or delete columns; inability to search from the back etc.
The XLOOKUP function is made available because it is sophisticated to perform lookup both vertically and horizontally. The new XLOOKUP is able to lookup value from the right and left conveniently. In addition, user can delete and insert columns without the formula breaking etc. Note, the VLOOKUP and HLOOKUP functions is available in the LOOKUP Function Library.
XLOOKUP syntax
=XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])
lookup_value: The specific value to lookup
look_array: The array where the lookup_value is located
return_array: The specific value to deliver based on the specified lookup_value and look_array
match_mode: Specify the matching to perform. Zero (0) performs exact match which is the default
search_mode: Specify the type and direction of search
The XLOOKUP function is currently available to office 365 insider’s edition users.