Open In App

XLOOKUP vs INDEX-MATCH in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

XLOOKUP can figure out the following more modest or the following bigger worth when there is no definite match. INDEX-MATCH can likewise do such, yet the lookup_array should be arranged in climbing or dropping requests. Both help match Wildcards. XLOOKUP can figure out either the first or the last worth when different qualities match.

XLOOKUP versus INDEX and MATCH

We have seen that INDEX/MATCH is a lot quicker than XLOOKUP.

Index-function

In this informational index, we’re taking a gander at a bunch of SKUs from an online business store, alongside the number of deals for each. In segment G we’ve made an equation that looks for the SKU in section B and takes out the comparing number of deals.

Separating the MATCH work

Match-function

 

The INDEX work takes two required contentions – the exhibit and the column number in the cluster. By contributing our deals cluster (D3:D10) and folding the INDEX work over the match work, the absolute Sales of 12 are returned in light of the fact that ’12’ is in the principal line of the exhibit. Subsequent to doing this a couple of times, getting the hang of INDEX+MATCH for this kind of situation is extremely simple. How about we perceive how XLOOKUP handles a similar issue…

XLOOKUP in Place of INDEX and MATCH

Xlookup-function

 

Right underneath our INDEX and MATCH recipe, I’ve input an XLOOKUP equation that endeavours to track down similar data. For this situation, it’s an unmistakable champ.

It takes 3 obligatory contentions

  1. Esteem
  2. Query Array (where to search for the worth)
  3. Return Array (what values to return)

For this situation, it accomplishes practically everything of INDEX/MATCH with less exertion. Yet, that is not where the advantages of XLOOKUP stop.

Comparison between the XLOOKUP function and the INDEX-MATCH Function

Presently we have separated the recipe, we should examine a few similitudes and dissimilarities between the two capacities. Below major points in a table are shown for your convenience.

Place of Discussion

Similar or Dissimilar

Clarification

Column lookup_array Similar Both help a section as the lookup_array.
Row lookup_array Similar Both help a row as the lookup_array.
No Matching of lookup_value Dissimilar XLOOKUP has the default setup choice for no matching of the lookup_value. Yet, the INDEX-MATCH doesn’t have.
Approximate match Partial Similarity XLOOKUP can figure out the following more modest or the following bigger worth when there is no accurate match. INDEX-MATCH can likewise do such, however, the lookup_array should be arranged in climbing or sliding request.
Matching Wildcards Similar Both help matching Wildcards.
Multiple Values Matching Partial Similarity XLOOKUP can figure out either the first or the last worth when different qualities match. Yet, INDEX-MATCH can return the principal esteem that matches.
Array Formula Similar Both support the array formula.

Last Updated : 16 Jun, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads