In this tutorial, you’ll learn how to use the XLOOKUP function in Excel 365, how it improves upon VLOOKUP, HLOOKUP, and INDEX/MATCH, and whether or not XLOOKUP will start appearing in spreadsheets everywhere.

Resources:

[ Ссылка ]

[ Ссылка ]

Table of Contents:

0:00 Introduction

1:10 Short Answer and Demonstration

3:15 How XLOOKUP Improves Upon VLOOKUP

5:31 XLOOKUP vs. VLOOKUP for Data Retrieval

8:22 XLOOKUP vs. INDEX/MATCH/MATCH

12:20 More Advanced Points

13:27 Recap and Summary

Lesson Outline:

XLOOKUP is intended to be an improved version of functions like VLOOKUP, HLOOKUP, and even the INDEX/MATCH combination – so, how well does it serve that role?

The short version is that it’s a clear improvement over VLOOKUP and HLOOKUP, as it’s more powerful and easier to use than those.

It lets you specify a search value, a “lookup array,” and a “return array,” and search in any direction of a range of cells without having to give a row or column number.

It also lets you change the match type (exact vs. approximate vs. others), the search order, and the behavior if no value is found.

Nested XLOOKUP functions are a bit shorter and faster to write than the INDEX/MATCH/MATCH combination, but it’s not *as big* an improvement here.

The main problem is that XLOOKUP doesn’t work in older versions of Excel, and companies tend to upgrade VERY slowly.

How XLOOKUP Fixes and Improves VLOOKUP

Although the VLOOKUP function is very common in spreadsheets, it has a number of problems, which is why we almost always recommend INDEX/MATCH instead (and XLOOKUP is now even better):

First, VLOOKUP can only search from left to right in a table of data, and the item you’re looking up must be in the leftmost column of that table.

Second, you need to specify a column number for the results you want VLOOKUP to return. This may not seem like a big deal, but if the data range or setup ever changes, the function will not be valid anymore.

Third, VLOOKUP has no built-in support for errors such as the data not being found or for different “search modes” such as going from first-to-last or last-to-first.

Finally, VLOOKUP and HLOOKUP are both slow functions that are possibly “volatile,” meaning that small changes elsewhere on the spreadsheet could cause these functions to recalculate, even if the recalculation is not necessary.

If you compare equivalent VLOOKUP and XLOOKUP formulas for calculating the Discounted Price after a School or Organization receives a discount on a product purchase in this data, you can see the difference:

=H3 * (1 – VLOOKUP(E3, Schools!$C$3:$E$17, COLUMNS(Schools!$C$2:$E$17), FALSE))

=H3 * (1 – XLOOKUP(E3, Schools!$C$3:$C$17, Schools!$E$3:$E$17, 0, 0))

The XLOOKUP version is faster and easier to write, it handles the case where there’s no match by returning a “0,” and it doesn’t require a column number or the COLUMNS function. Instead, you directly enter the “answer” or “results” column.

XLOOKUP vs. INDEX/MATCH/MATCH

The INDEX/MATCH/MATCH combo is most useful when you have to perform a 2-way lookup in a table by the row number and column number.

For example, in the “Schools” tab here, maybe we want to write a function that outputs *any* parameter for *any* School or Organization based on the Promo Code and the Parameter Name.

With error-checking included, the INDEX/MATCH/MATCH version looks like this:

=IFERROR(INDEX($B$2:$E$17, MATCH(C20,$C$2:$C$17, 0), MATCH(B21,$B$2:$E$2, 0)), “N/A”)

The MATCH functions find the positions of the Parameter and the School/Organization, and then the INDEX function returns the value at the intersection of this row and column in the table.

The IFERROR around the entire formula makes it return “N/A” if the Promo Code or Parameter Name are not found.

The XLOOKUP version uses a nested XLOOKUP and is a bit shorter to write:

=XLOOKUP(B21, $B$2:$E$2, XLOOKUP(C20,$C$2:$C$17, $B$2:$E$17, "N/A"))

First, it finds the position of the Parameter Name in the row at the top.

Then, the second XLOOKUP function finds the position of the Promo Code in the Promo Code column and returns the entire matching row.

Then, the first XLOOKUP combines the position of the Parameter Name with this single row result to return the parameter value, such as the commission rate or group discount rate for this specific School or Organization.

свернуть