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

Microsoft Excel’s dynamic array perform XLOOKUP() would possibly fully substitute VLOOKUP() and HLOOKUP().


Picture: 200dgr/Shutterstock

Microsoft Excel’s lookup features are highly effective however typically misunderstood as a result of they’ve a couple of behaviors that appear a bit opposite to what customers anticipate. You utilize these features if you wish to discover values based mostly on the worth in a corresponding cell. Because of the newish dynamic array perform XLOOKUP(), you now have extra energy and fewer confusion. On this article, we’ll evaluate XLOOKUP() to VLOOKUP() so you may see how XLOOKUP() excels and begin utilizing it straight away.

SEE: 83 Excel suggestions each consumer ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system. This newish perform is obtainable in Microsoft 365 and Excel 2021, and Excel for the online. To your comfort, you may obtain the demonstration .xlsx file. This text assumes that you’ve fundamental Excel abilities, however even a newbie ought to be capable of observe the directions to success.

About XLOOKUP() in Excel

XLOOKUP() is considered one of a number of newish dynamic array features. In case you’ve ever entered an expression utilizing Ctrl + Shift + Enter, then you definitely’re already acquainted with how Excel used to work with dynamic arrays. Because of the brand new dynamic array function, most of these expressions are a lot simpler to create and preserve as a result of you may enter the expression as you usually would—with a easy Enter. The outcomes spill into the cells beneath, filling as many as mandatory to finish the expression’s calculations. That is referred to as the spill vary. In case you see a spill error, then the vary wanted to satisfy the perform is not out there. What this implies is that you should utilize one perform to return a number of columns (or rows) of ensuing values.

XLOOKUP() returns knowledge in a desk or vary by row. You would possibly wish to return the worth of a product or a consumer’s telephone quantity. Utilizing XLOOKUP(), you may rapidly retrieve data based mostly on a search time period in a corresponding cell.   

Listed here are just some of XLOOKUP’s upgrades:

  • XLOOKUP() helps vertical and horizontal lookups.
  • XLOOKUP() searches to the left and proper, so no extra rearranging columns.
  • XLOOKUP() helps relative references so you may insert and delete columns (or rows) and the perform will replace accordingly.
  • XLOOKUP() defaults to an actual match, which is the popular default; the older lookup features default to the closest match.
  • XLOOKUP()’s new match mode permits extra versatile searches.

SEE: Home windows 11: Recommendations on set up, safety and extra (free PDF) (TechRepublic)

Now let’s check out this perform’s syntax:

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

The primary three arguments are required:

  • lookup_value: The search time period.
  • lookup_array: The search vary (or the supply knowledge).
  • return_array: The return vary (or the outcomes).
  • if_not_found: Textual content returned when a sound match is not discovered. If omitted, the perform returns #N/A.
  • match_mode: Specifies the match sort. See Desk A for the suitable values and explanations.
  • search_mode: Specifies the search mode. See Desk B for the suitable values and explanations.

Desk A

Worth Rationalization
0 Discover an actual match; return #N/A if none discovered. That is the argument’s default worth.
-1 Discover an actual match; return the subsequent smaller merchandise if no match is discovered.
1 Discover an actual match; return the subsequent bigger merchandise if no match is discovered.
2 Permits a wildcard match: *, ?, and ~.

Desk B

Worth Rationalization
1 Begin search with the primary merchandise. That is the default worth for this argument.
-1 Begin search with the final merchandise.
2 Search requires that lookup_array is sorted in ascending order. If not, the perform returns invalid outcomes, not an error.
-2 Search requires that lookup_array is sorted in descending order. If not, the perform returns invalid outcomes, not an error.

That is a variety of data, however most of it’s just like the older lookup features. Now, let’s transfer on to some examples.

A fast comparability of XLOOKUP() and VLOOKUP()

XLOOKUP() can be utilized to return a single worth, equally to VLOOKUP(), however it makes use of completely different arguments. Let’s evaluate the 2 features utilizing the information set in Determine A. Particularly, we’ll return the worker ID and the date utilizing the personnel worth because the search time period (K1).

Determine A


  We’ll use lookup features to return values based mostly on a search time period.

First, let’s evaluation the ID features:

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

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

Each features use the worth in K1, Luke, because the search time period. A very powerful factor to say is that the VLOOKUP() perform in K3 returns the improper worth, whereas the XLOOKUP() perform in K4 returns the right worth. VLOOKUP() requires a sorted knowledge set, however XLOOKUP() does not. XLOOKUP() returns the primary worth that matches—the default settings.

The features in L3 and L4 try and return the date based mostly on the lookup worth, Luke, utilizing the features

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

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

You in all probability anticipated the error worth in L3 as a result of VLOOKUP() does not assist a search to the left of the lookup worth; the perform merely does not perceive the argument, -1. XLOOKUP() does, and as an alternative of utilizing a detrimental worth, you reference the precise column and once more, the perform does not thoughts that the information set is not sorted. On this easy instance, sorting the information would not matter, however generally you need to work with the information set order, so this new conduct is a superb improve.

Keep tuned

At this level, you can begin utilizing XLOOKUP() as an alternative of the older lookup features, when you like. You may have sufficient data to get began. Don’t fret about changing the older lookup features; it is uncertain that Microsoft will deprecate them within the close to future. 

In my subsequent article, we’ll use superior options to make use of a number of standards with XLOOKUP(). We’ll additionally learn to return a number of columns with one XLOOKUP() perform. 

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox