All Resources

XLOOKUP in Excel 365 – Same Same, but Different

This article introduces one of Excel’s most anticipated new feature releases – the XLOOKUP function. But to do that, we should start with a little history.

For decades, the VLOOKUP function has been a workhorse of Excel, easily the most widely implemented and broadly understood of the lookup family of functions. It has also been the target of many expletive-laced tirades when a “helpful” co-worker changes your dataset without telling you, causing all of your perfect lookup tables to cease functioning. 

In order to locate a desired cell in a database, the VLOOKUP function will look for a particular “lookup_value” in the left-most column of a table (the “table_array”) that you define. When it finds a match it will return data from a specific cell on that row as indicated by a column number (the “col_index_num”). To see it at work, let’s look at an example:

In this example, a user can enter a name in cell B9 (the lookup_value) and the VLOOKUP function in cell C9 will search for that name in the left-hand column of the table B1:D6 (the table_array) and return whatever is in the second column (the col_index_num) of that row of the table (the Math Grade). Note that the table_array must start in Column B (where the lookup_value is located) and excludes the dates in Column A.

VLOOKUP has worked for decades, but Microsoft has always been aware that the function has limitations and has been working on a way to improve VLOOKUP.

Key Limitations of VLOOKUP

  1. The lookup_value and the cell containing the data you want to extract must both be contained within the same table_array; for example, you could not have a VLOOKUP that looks for a Student Name on one sheet of a workbook and a returns a Math Grade if they were stored on another worksheet
  2. The cell that contains the data you want to extract must be located to the right of the lookup_value; in the above example, you cannot lookup the Student Name and return the Date Updated using a VLOOKUP
  3. Inserting new cells in a table referred to with VLOOKUP functions will cause incorrect results to be returned unless you have automated the col_index_num argument; this is a very common issue in lookup tables
  4. By default (i.e. if you don’t specify the final “range_lookup” argument), VLOOKUP will return an approximate match; this will cause incorrect results in many cases
  5. If you want to look along the top row of a table, rather than the left-most column, you must learn and use a separate function (HLOOKUP)

XLOOKUP has been designed by Microsoft to overcome many of these shortcomings.

Introduction to XLOOKUP

XLOOKUP (along with other functionality) was released to the current channel of Microsoft 365 in early 2020 after a long period of beta testing. This function is intended to ultimately replace both VLOOKUP and HLOOKUP and can also take the place of combinations of some of Excel’s other lookup functions, including OFFSET, INDEX & MATCH.

The most important aspect of XLOOKUP is that it allows us to separate the lookup_array (where Excel looks for the lookup_value) from the return_array (where Excel gets the data from). The two related pieces of data no longer need to be in the same table_array, or even on the same worksheet.

In the simplest form, XLOOKUP asks you to enter the following:

  • lookup_value: what you want Excel to search for;
  • lookup_array: where you want Excel to search for the lookup_value; and
  • return_array: where you want Excel to extract information from once it finds a match.

In the simple example below, the XLOOKUP function in cell C9 looks for the Selected Name (the lookup_value in cell B9) in the list of Student Names (the lookup_array in cells B2:B6) and when it finds an exact match, it returns the corresponding piece of information in the list of Math Grades (the return_array in cells C2:C6).

Why do we need another lookup function?

At first, this doesn’t seem that much better than VLOOKUP, aside from requiring one less argument. But there are significant advantages to using XLOOKUP that are worth highlighting:

  1. Counting & hardcoding column numbers is hard and prone to error. So is keeping meddling users from adding columns to your data and wreaking havoc with all of your masterful VLOOKUP functions. Luckily, we don’t have to worry about either with XLOOKUP. Since the function refers explicitly to an array of data to return from, the column number is no longer necessary. If you add new columns, your “return_array” reference will shift, just like it does with any other Excel function.


    2. As mentioned under “Limitations of VLOOKUP”, the default setting under the older function is to look for an “approximate match.” In very particular circumstances this could be advantageous, but it will usually return unexpected results. With XLOOKUP, the default condition is to look for an exact match. This should help avoid errors.


    3. To paraphrase the mighty Beyonce, “to the left, to the left”; we are now able to return information to the left of the lookup_array, no more need to re-order how our data is stored in order to utilize VLOOKUP or resort to more complicated lookup functions.


    4. We all make mistakes. As Dr. Donald Berwick (the former President and CEO of the Institute for Healthcare Improvement) said “accept human error as inevitable—and design around that fact.”  Spreadsheet errors tend to be slightly less serious than errors in healthcare (the NHS COVID reporting error demonstrated that they are not mutually exclusive), but we would be wise to follow Dr. Berwick’s advice. XLOOKUP has built-in error-handling capabilities that will return a defined message or value if it is unable to find your lookup_value (rather than the #N/A error VLOOKUP returns).

    5. Previously we had to use a separate function (HLOOKUP) to search for a match in the top row rather than the left column. XLOOKUP is now indifferent to the orientation of the lookup_array, so it can replace both VLOOKUP and HLOOKUP. Excellent!


Advanced uses for XLOOKUP

The above reasons make XLOOKUP a great tool for any regular Excel user, but the advanced implementations are what gets us excited about this function.

1. XLOOKUP is able to take advantage of the spilling features of Microsoft 365 (see our earlier Benchmarq article for more information). If we enter a return_array that is multiple columns wide, we can use XLOOKUP to return an entire row of data with a single formula.


2. Based on the spilling example above, if we combine two XLOOKUP functions, we can pull an exact match based on two lookups. For example, we could set up a table that would allow a user to enter the desired info and the student name and pull the single piece of information. Veteran Excel users will recognize this as a simpler replacement for the INDEX/MATCH/MATCH lookup formula combination.


3. The final 2 arguments in the XLOOKUP (match_mode and search_mode) give users new ways to search for data. With VLOOKUP, the only options were for an “exact” or “approximate” match method. XLOOKUP introduces the ability to search using wildcard characters (i.e. * and ?) and to change the way that Excel searches for approximate matches. In addition, we can now specify whether the function should start searching at the start or the end of our table (which is helpful when there may be multiple instances of the lookup_value in our lookup_array).

Where to learn more…  and some cautions

This article is already long enough, and we could go into far more detail. But if XLOOKUP (and the other new functionality in Microsoft 365) is interesting and if you’d like to learn more and practice these skills, sign up for one of our upcoming half-day classes on Excel 365. There are dates in late February and in April. Alternatively, if you have a group of up to 18 of your closest friends (or co-workers) that want to learn all about these features and more, reach out to us at info@marqueegroup.ca to talk about a private (online) session.

Finally, a word of warning:  Just like the dynamic arrays features in Excel, XLOOKUP is not backward compatible with earlier versions of Excel (i.e. 2019, 2016, etc.). If you are working in an environment where you know everyone is using Microsoft 365, XLOOKUP can be a great function to roll out. But if you are creating files that may need to be shared with users of legacy versions of Excel, it may be best to wait until Microsoft 365 is adopted more widely.