I have looked at VLOOKUP, HLOOKUP, INDEX and MATCH in recent months and they all have their relative strengths and weaknesses.  However, XLOOKUP is arguably the strongest all-round performer, but it is only available in Excel 365, Excel Online, Excel for Tablets, Excel for Mobile and Excel 2021 / later versions.  It’s definitely worth discussing though!

The syntax for XLOOKUP is as follows:

XLOOKUP(lookup_value, lookup_vector, results_array, [if_not_found], [match_mode], [search_mode])

This function seeks out a lookup_value in the lookup_vector and returns the corresponding value in the results_array.  Similar to RANDARRAY, Microsoft again decided to make a change before pulling the pin and make both of these functions Generally Available.  The line of thinking is that there should be an error trap for when a value cannot be found.  Having said that, most of the time you will only require the first three arguments:

• lookup_value: this is required and defines what value you want to look up
• lookup_vector: this reference is required and is the row or column of data you are referencing to look up lookup_value
• results_array: this is where the corresponding item is you wish to return and is also required (even if it is the same as lookup_vector).  This does not have to be a vector (i.e. one row or one column of cells): it may be an array (with at least two rows and at least two columns of cells).  The only stipulation is that the number of rows / columns must equal the number of rows / columns in the column / row vector – but more on that later
• if_not_found: this optional argument allows you to replace the usual return of #N/A with something more informative like an alternative formula, text or a value
• match_mode: this argument is optional.  There are four choices:
       0: exact match (default)
      -1: exact match or else the largest value less than or equal to lookup_value
       1: exact match or else smallest value greater than or equal to lookup_value
       2: wildcard match.  You should use the special character ? to match any character and * to match any run of characters.
  What’s impressive, though, is that for certain selections of the final argument (search_mode), you don’t need to put your data in alphanumerical order!  As far as I am aware, this is a first for Excel
• search_mode: this argument is also optional.  There are again four choices:
       1: search first to last (default)
      -1: search last to first
       2: what is known as a binary search, first to last (requires lookup_vector to be sorted).  Just so you know, a binary search is a search algorithm that finds the position of a target value within a sorted array.  A binary search compares the target value to the middle element of the array.  If they are not equal, the half in which the target cannot lie is eliminated and the search continues on the remaining half, again taking the middle element to compare to the target value, and repeating this until the target value is found
      -2: another binary search, this time last to first (and again, this requires lookup_vector to be sorted).

Let’s have a look at XLOOKUP versus VLOOKUP:

(Examples are visible in the attached Excel file.)

You can clearly see the XLOOKUP function is shorter:

=XLOOKUP(H52,F41:F47,G41:G47)

 Only the first three arguments are needed, whereas VLOOKUP requires both a fourth argument, and, for full flexibility, the COLUMNS function as well. XLOOKUP will automatically update if rows / columns are inserted or deleted.  It’s just simpler.

HLOOKUP has similar issues:

Here, this highlights what happens if I try to deduce the student’s name from the Student ID. HLOOKUP cannot refer to earlier rows, just as VLOOKUP cannot consider columns to the left. Given any unused elements of the table are ignored also, it’s just good news all round.  Goodbye limitations, hello XLOOKUP.

Indeed, things get even more interesting when you start considering XLOOKUP’s final two arguments, namely match_mode and search_mode, viz.

Notice that I am searching the ‘Value’ column, which is neither sorted nor contains unique items.  Do you see how the results have changed once more, depending upon match_mode and search_mode?

The match_mode zero (0) returns “Not Found” now instead of #N/A because there is no exact match and the formula has now stipulated what to do in such an instance.

When match_mode is -1, XLOOKUP seeks an exact match or else the largest value less than or equal to lookup_value (6.5).  That would be 4 – but this occurs more than once (B and D both have a value of 4).  XLOOKUP chooses depending upon whether it is searching top down (search_mode 1, where B will be identified first) or bottom up (search_mode -1, where D will be identified first).  Note that with binary searches (with a search_mode of 2 or -2), the data needs to be sorted.  It isn’t – hence we have garbage answers that cannot be relied upon.

With match_mode 1, the result is clearer cut.  Only one value is the smallest value greater than or equal to 6.5.  That is 7, and is related to A.  Again, binary search results should be ignored, although it is worth noting “Not Found” occurs when Excel identifies the lookup value has not been found.

The match_mode 2 results are spurious.  This is seeking wildcard matches, but there are no matches, hence “Not Found” instead of N/A for the only search_modes that may be seen as creditable (1 and -1).  It’s interesting to note a binary search causes errors which are not trapped by the new argument.

Clearly binary searches are higher maintenance.  In the past, it was worth investing in them as they did return results more quickly.  However, according to Microsoft, this is no longer the case: apparently, there is “…no significant benefit to using (sic) the binary search options…”.  If this is indeed the case, then I would strongly recommend not using them going forward with XLOOKUP.

The next article in the series will explore comparisons with LOOKUP.