XLOOKUP - NEW FEATURE IN MS-EXCEL

XLOOKUP, NEW FEATURE IN MS-EXCEL XLOOKUP

XLOOKUP - NEW FEATURE IN MS-EXCEL

     Microsoft introduced a new feature called XLOOKUP, which was designed to overcome some of the shortcomings of one of Excel’s most popular features, VLOOKUP.

     Since releasing version 1.0 of Excel for Macintosh in 1985, the EXCEL included VLOOKUP to help users carry over information from one part of a spreadsheet to another. It was the third most-used function, after SUM and AVERAGE. The 'V' in VLOOKUP stands for vertical, a reference to the function’s ability to retrieve information in vertical columns. A similar function called HLOOKUP works with data that’s in horizontal rows.

     VLOOKUP had some notable limitations. Results defaulted to an approximate match of what the user was looking for, instead of the exact match. XLOOKUP fixes that.

Introducing XLOOKUP

     XLOOKUP is named for its ability to look both vertically and horizontally (yes it replaces HLOOKUP also). In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). Let’s consider its syntax in it's simplest form:

XLOOKUP(lookup_value,lookup_array,return_array)

• lookup_value: What you are looking for
• lookup_array: Where to find it
• return_array: What to return

Advanced XLOOKUP

To perform advanced lookups, you can use XLOOKUP’s optional 4th and 5th mode arguments: match_mode and search_mode.

XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

match_mode allows you to set the type of match you’d like to perform. The options are:

• Use zero to perform an exact match. This is the default.
• Use 1 or -1 to allow a match against the nearest smaller (or larger) item when there is no exact match.
• Use 2 to do a simple wildcard match where ? means match any character and * means match any run of characters.

search_mode lets you configure the type and direction of search. The options are: 

Use 1 or -1 to search from first-to-last or last-to-first.
Use 2 or -2 to do a binary search on sorted data.

Why need a new lookup function?

While VLOOKUP was widely used, it has several well-known limitations which XLOOKUP overcomes:

Defaults to an “approximate” match: Most often users want an exact match, but this is not VLOOKUP’s default behavior. To perform an exact match, you need to set the 4th argument to FALSE. If you forget (which is easy to do), you’ll probably get the wrong answer.

 
Does not support column insertions/deletions: VLOOKUP’s 3rd argument is the column number you’d like returned. Because this is a number, if you insert or delete a column you need to increment or decrement the column number inside the VLOOKUP. 

Cannot look to the left: VLOOKUP always searches the 1st column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data. 

Cannot search from the back: If you want to find the last occurrence, you need to reverse the order of your data. 

Cannot search for next larger item: When performing an “approximate” match, only the next smaller item can be returned and only if correctly sorted. 

References more cells than necessary: VLOOKUP 2nd argument, table_array, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it truly depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets. 

XLOOKUP EXAMPLE