XLOOKUP and INDEX-MATCH both retrieve data efficiently, support wildcards, and handle approximate matches. XLOOKUP adds extra benefits, including default results for missing values and options to return the first or last match, while simplifying the formula.YE
We have seen that INDEX/MATCH is a lot quicker than XLOOKUP.
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.
Using INDEX+MATCH
The INDEX function needs two arguments: the array and the row/column number. By combining INDEX with MATCH on the sales range (D3:D10), we can return the total sales for a given SKU. After a few tries, using INDEX+MATCH becomes straightforward. Next, let’s see how XLOOKUP handles this.
Using XLOOKUP
Below the INDEX+MATCH formula, an XLOOKUP formula retrieves the same data with less effort. It requires three arguments:
- Value: What to look for
- Lookup Array: Where to search
- Return Array: What to return
XLOOKUP handles almost everything INDEX+MATCH does, while offering additional benefits.
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. |