XLOOKUP
top of page
Search
    • Sep 8, 2019

XLOOKUP


Take note that Microsoft is planning to release a revised version of its workhouse VLOOKUP function for MS Excel. The new function is called XLOOKUP. It's currently only available for Office 365 subscribers with special insider status, but it should be released more widely before the end of the year.

XLOOKUP follows this basic format:

=XLOOKUP(value, array, return_array, match setting, search setting)

No longer will users have to specify the number of the column in a long array from which a value should be returned. The new XLOOKUP function also allows for fuzzy searches to be run.

The first entry in the formula, the 'value', is a reference to the cell containing the string you want to look up - just like with VLOOKUP.

The second entry is the column where the value to be found is located. 'C1:C100', or whatever.

The third entry is the column where the value to be returned can be located. E.g., 'Z1:Z100'. It's not necessary to enter an array C1:Z100, and then count how many columns Z is from C. Another improvement over VLOOKUP is that the value to be returned can be in a column to the left of the value being looked up. E.g., in 'B1:B100'.

The fourth entry is not limited to the kind of match settings in VLOOKUP - either TRUE or FALSE. A zero here will require an exact match. '-1' will find an exact match or a number closed to the search for value that is not larger than it. '1' will find an exact match for a number or the number that is closest to it without being smaller. A '2' setting will provide a real innovation in allowing for a fuzzy search to be performed.

The fifth entry is for the 'search mode'. A '1' runs a search for the first occurrence of the looked up value starting at the top of the column in the lookup array. A '-1' runs a search for the first occurrence of the looked up value starting from the bottom of the column in the lookup array. A '2' or '-2' will serve the same purpose, but cause a binary search to be run which is quicker - it relies on the data being sorted in ascending or descending order.


bottom of page