How to use the newish XLOOKUP() dynamic array function in Excel

Microsoft Excel’s lookup functions are powerful but often misunderstood because they have a few behaviors that seem a bit contrary to what users expect. You use these functions when you want to find values based on the value in a corresponding cell. Thanks to the newish dynamic array function XLOOKUP(), you now have more power and less confusion. In this article, we’ll compare XLOOKUP() to VLOOKUP() so you can see how XLOOKUP() excels and start using it right away.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. This newish function is available in Microsoft 365 and Excel 2021, and Excel for the web. For your convenience, you can download the demonstration .xlsx file. This article assumes that you have basic Excel skills, but even a beginner should be able to follow the instructions to success.

About XLOOKUP() in Excel

XLOOKUP() is one of several newish dynamic array functions. If you’ve ever entered an expression using Ctrl + Shift + Enter, then you’re already familiar with how Excel used to work with dynamic arrays. Thanks to the new dynamic array feature, these types of expressions are much easier to create and maintain because you can enter the expression as you normally would—with a simple Enter. The results spill into the cells below, filling as many as necessary to complete the expression’s calculations. That’s called the spill range. If you see a spill error, then the range needed to fulfill the function isn’t available. What this means is that you can use one function to return multiple columns (or rows) of resulting values.

XLOOKUP() returns data in a table or range by row. You might want to return the price of a product or a client’s phone number. Using XLOOKUP(), you can quickly retrieve information based on a search term in a corresponding cell.   

Here are just a few of XLOOKUP’s upgrades:

• XLOOKUP() supports vertical and horizontal lookups.
• XLOOKUP() searches to the left and right, so no more rearranging columns.
• XLOOKUP() supports relative references so you can insert and delete columns (or rows) and the function will update accordingly.
• XLOOKUP() defaults to an exact match, which is the preferred default; the older lookup functions default to the nearest match.
• XLOOKUP()’s new match mode allows more flexible searches.

SEE: Windows 11: Tips on installation, security and more (free PDF) (TechRepublic)

Now let’s take a look at this function’s syntax:

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

The first three arguments are required:

• lookup_value: The search term.
• lookup_array: The search range (or the source data).
• return_array: The return range (or the results).
• if_not_found: Text returned when a valid match isn’t found. If omitted, the function returns #N/A.
• match_mode: Specifies the match type. See Table A for the appropriate values and explanations.
• search_mode: Specifies the search mode. See Table B for the appropriate values and explanations.

Table A

Table B

That’s a lot of information, but most of it is similar to the older lookup functions. Now, let’s move on to a few examples.

A quick comparison of XLOOKUP() and VLOOKUP()

XLOOKUP() can be used to return a single value, similarly to VLOOKUP(), but it uses different arguments. Let’s compare the two functions using the data set in Figure A. Specifically, we’ll return the employee ID and the date using the personnel value as the search term (K1).

Figure A

First, let’s review the ID functions:

K3: =VLOOKUP($K$1,Table1[[Personnel]:[ID]],2)

K4: =XLOOKUP($K$1,Table1[Personnel],Table1[ID])

Both functions use the value in K1, Luke, as the search term. The most important thing to mention is that the VLOOKUP() function in K3 returns the wrong value, whereas the XLOOKUP() function in K4 returns the correct value. VLOOKUP() requires a sorted data set, but XLOOKUP() doesn’t. XLOOKUP() returns the first value that matches—the default settings.

The functions in L3 and L4 attempt to return the date based on the lookup value, Luke, using the functions

L3: =VLOOKUP($K$1,Table1[[Personnel]:[ID]],-1)

L4: =XLOOKUP($K$1,Table1[Personnel],Table1[Date])

You probably expected the error value in L3 because VLOOKUP() doesn’t support a search to the left of the lookup value; the function simply doesn’t understand the argument, -1. XLOOKUP() does, and instead of using a negative value, you reference the actual column and again, the function doesn’t mind that the data set isn’t sorted. In this simple example, sorting the data wouldn’t matter, but sometimes you have to work with the data set order, so this new behavior is a great upgrade.

Stay tuned

At this point, you can start using XLOOKUP() instead of the older lookup functions, if you like. You have enough information to get started. Don’t worry about replacing the older lookup functions; it’s doubtful that Microsoft will deprecate them in the near future. 

In my next article, we’ll use advanced features to use multiple criteria with XLOOKUP(). We’ll also learn how to return multiple columns with one XLOOKUP() function. 

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see