search
top

XLOOKUP Function

ShareShare on FacebookShare on Google+Email this to someoneTweet about this on TwitterShare on LinkedIn

You might have seen people struggling to find matching data in excel sheets and getting confused over whether to use VLOOKUP or INDEX-MATCH combination. I remember, around a decade back, while I was conducting a data analysis workshop, a participant asked me why Excel does not have an easy function to look up values in table irrespective of its vertical or horizontal arrangement. Why is it so rigid about searching and returning row/column indices? I admitted that there should have been a better function, but it was not!! Finally, an easy to use, universal substitute of VLOOKUP and HLOOKUP function has come up to serve all our lookup needs without thinking of alternative functions.

 
Microsoft has announced one of the biggest updates in MS Excel in September 2019. It’s a function under “Lookup and Reference Functions”, called XLOOKUP, which is designed to address some of the shortcomings of one of Excel’s most popular features, VLOOKUP. The function is only available to Office 365 testers, and Microsoft will make it broadly available over time.

 
The most widely used Lookup functions are VLOOKUP and HLOOKUP. VLOOKUP stands for vertical look up, provides the ability to retrieve information in vertical columns, while HLOOKUP works with data arranged in horizontal rows.

 

Need for a New Lookup Function

The answer to this question is Simple!! It’s to make the find and fill value requirement dramatically easy for excel users. Some of the difficulties excel users found with earlier lookup functions are:

  • Lookup can only find the matching values on the right.
  • It only returns the first match.
  • It worked on a static column serial number to look up data in a dynamic table.
  • It is sensitive to the last argument value; by default an approximate match is returned unless a 0 value is passed in the last argument.
  • Looking for the nearest greater value was impossible.</li>

Hence, to get rid of the pain points listed above, a better lookup functions was required.

 

The XLOOKUP function

The new XLOOKUP function combines VLOOKUP and HLOOKUP into a single function allowing users to look up for required data available in left or right row or column. This function can be called by typing in the formula bar

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

The XLookUp function takes 5 arguments; 3 compulsory and 2 optional. Let us look at the arguments:

  1. lookup_value: is the value you are looking for. (same as in VLOOKUP function)
  2. lookup_array: is the array in which you are looking for lookup_value. It can either be data in a column or in a row. It’s where the XLOOKUP will look for the match (No need to apply VLOOKUP for column data or HLOOKUP for row data)
  3. return_array: is the array returned as a result of lookup. The return array can either be a row data or a column data. Importantly, this array needs not to be to the right of the lookup_array.
  4. match_mode: is the optional argument that gives you variety of matching options rather than limiting to TRUE and FALSE in VLOOKUP:
    • 0 is the default behavior, in case you didn’t know
    • 1 will return the nearest smaller item (if there is no exact match!)
    • -1 will return the first larger item (if there is no exact match!)
    • 2 gives you more control over searching by the use of simple wildcards to filter in Excel
      • ? replaces any one character
      • * replaces any number of any characters (number can be zero)
    1. search_mode: is another optional argument that allows you to either use 1 to search from first row/column to last or -1 to search from last row/column to first.

     

    XLookup Example
    (Courtesy: XLOOKUP function – Office Support)

    Benefits Revisited

    If you are still not clear why a new lookup function has arrived, here are some attractive features:

    • Less number of steps are always easy to remember.
      VLOOKUP required four arguments whereas XLOOKUP requires just three arguments, and they follow a logical, easy-to-remember order.
    • Not necessarily searching on the right of search columns.
      VLOOKUP was restricted to return the search results from columns to the right of a reference column. This means that you had to rearrange your data before working with it. In case of XLOOKUP, values can be looked up on right, left, up, or down w.r.t. reference row/column.
    • Relieves you from counting column numbers.
      VLOOKUP required you to pass the column number of the data from which you wanted data to be returned. This created problem when a column was inserted or deleted from the lookup array. Your formula could also get clumsy if you don’t change the column index accordingly. On the other hand, XLOOKUP doesn’t rely on column numbers. This saves you from headache of changing the formula for every column change in the lookup table.

    Hence, instead of juggling between lookup functions when the arrangement of data in your table changes, this function will let you get faster lookup results.

    Leave a Reply

top