Introduction to XLOOKUP

Availability : Excel 2021, Excel for Microsoft 365, Excel for the Web, Excel 2021 for Mac, Excel for Microsoft 365 for Mac

The XLOOKUP function combines a blend of the key attributes of the INDEX and MATCH functions into one function. It is a brilliant function with some special abilities.

Its modest definition is that it looks for a value, within a range or array, and returns the corresponding item from a second range or array. However, you soon realize it goes a little beyond this scope.

The XLOOKUP function is especially well equipped at performing lookups on data in tables, returning ranges and working with dynamic arrays. These are key qualities of XLOOKUP.

Lookup functions such as INDEX, VLOOKUP, HLOOKUP, and OFFSET request index numbers for their row and column arguments. This makes them great for relative references and more flexible. XLOOKUP works with explicit references to the range or table element to be used for its lookup and return arrays. XLOOKUP is a perfect match for use with structured data in tables. They go together like mint and chocolate. A dream team.

XLOOKUP for an Exact Match

XLOOKUP makes exact match lookups very simple and very durable, especially when used with data formatted as a table.

Figure 12-1 shows a table of product data named [tblProductData]. This table can be found on the [Exact Match Data] sheet. It contains a unique ID for each product and a couple of attribute columns – [Category] and [Product Name].

The [ID] column of the lookup table [tblProductData] has been specified as the lookup array, and the return array is stated as the [Product Name] column.

This is very simple. There is no requirement to enter a column index number as requested by VLOOKUP or to nest a MATCH function as commonly used with INDEX.

Specifying table columns instead of ranges that lack meaning, such as column 5 or range E2:E14, makes it even better. It is faster to enter the formula too – less clicking with a mouse to select ranges, typing in meaningful references instead.

The XLOOKUP function has a built-in argument to specify an if not found value to return instead of the #N/A error. This is optional and is often not required.

In this example, the #N/A error makes it easier to spot the issue with the lookup value, but sometimes you want an alternative response. There can be valid reasons why a lookup value is not matched.

Because the XLOOKUP function uses distinct lookup and return arrays (VLOOKUP looks in and returns from the same table array), the lookup array and return array can be in any order within the lookup table.

In Figure 12-4, the following formula returns the [Category] for each product ID. You can see back in Figure 12-1 that the [Category] column sits to the left of the [ID] column in [tblProductData]. This is no problem for XLOOKUP.

=XLOOKUP(C2,tblProductData[ID],tblProductData[Category])

XLOOKUP to Look Up a Value in Ranges

The different match modes of XLOOKUP can be split into three different types of lookups – exact matches, range lookups, and wildcard lookups.

There are two types of range lookup – exact match or next smaller item and exact match or next larger item (Figure 12-5). Returning the next smaller item when a match is not found (–1) is the more commonplace of the two.

Figure 12-6 shows a table named [tblDiscounts] on a sheet named [Range Lookup Data]. It contains ranges of quantity values in a column named [Qty] and a [Discount] value associated with that range.

A 0 is entered for the if not found argument, and –1 is specified for the match mode.

Although the ranges of [Qty] values in [tblDiscounts] are in ascending order, and it is great that they are, the XLOOKUP function returns the correct discount regardless of the order of the ranges.

This provides a more robust range lookup formula than offered by the older VLOOKUP and MATCH functions. They required the range values to be in order.

XLOOKUP with Wildcards

The XLOOKUP function can handle the use of wildcard characters such as the asterisk (*) and question mark (?) in the lookup value, but this needs to be specified as the match mode.

Figure 12-8 shows a table named [tblTargets] on a sheet named [Target Data]. It contains names of cities and a target value associated with the city. The [City] column includes the country that the city resides in.

From another range, we have a column that contains city names only, for example, “Lisbon,” and we need to look up the city in the [tblTargets] table and return the [Target] value.

We need to perform a partial match on the city name, as there is not an exact match between the two column values. For this, we can perform a wildcard search.

For the lookup value, the city name in column C is combined with the asterisk wildcard character. This creates a lookup value that begins with the value in column C but can have any text after that.

The if not found argument is skipped, and 2 (wildcard character match) is entered for the match mode argument.

XLOOKUP for the Last Match

One of the key developments with the XLOOKUP function compared to some other lookup functions is its ability to search its lookup array from last to first in addition to the standard first to last.

Figure 12-10 shows a table named [tblCourses] that contains a list of training courses that were conducted recently. The courses are ordered by the date that they were delivered.

We want to specify a training course from a drop-down list and return the date and number of attendees for the last instance of that training course.

If no matching courses are found, the text “No courses booked” is returned. A 0 is entered to request an exact match, and –1 is entered to specify a last-to-first search mode.

An empty string “” is specified for the if not found argument. The XLOOKUP formula in cell F3 returns “No courses booked” if there is no matching value, so it is not necessary to provide a meaningful if not found response again. We just want to hide the #N/A error, and an empty string is ideal for that task.

Multiple Column XLOOKUP

With XLOOKUP, it is simple to create lookup formulas that return values or ranges where the values in multiple columns must match. These formulas are sometimes referred to as multi-criteria lookup formulas.

Figure 12-12 shows a table named [tblPrices], found on the [Multi-Column Data] sheet. It contains membership prices that are dependent upon the membership type and the location of the membership.

The values in cells B2 (membership) and C2 (location) are combined into one value using the ampersand (&). The same technique is applied to the columns required for the lookup array – [Membership] and [Location].

This simple technique is used to combine any number of criteria needed for the lookup value and lookup array.

Two-Way Lookup with XLOOKUP

What is better than XLOOKUP? Two XLOOKUPs. And with two XLOOKUP functions, we can create a two-way lookup.

This two-way lookup formula uses an XLOOKUP to return the matching value from an array returned by an accompanying XLOOKUP function.

Figure 12-14 shows the prices for holiday accommodation based on the type of accommodation and the location. The accommodation types are stored in range B3:B7, and the locations are stored in range C2:F2, on the worksheet [Prices].

In Figure 12-15, the following formula returns the prices for the given accommodation and location to the [Price] column in the table:

=XLOOKUP([@Accommodation],Prices!$B$3:$B$7,

XLOOKUP([@Location],Prices!$C$2:$F$2,Prices!$C$3:$F$7)

)

The second XLOOKUP is finding the matching column, while the first finds the matching row.

The second XLOOKUP function is used as the return array for the first XLOOKUP. The second XLOOKUP returns the array of values corresponding to the value stated in the [Location] column.

The first XLOOKUP then returns the corresponding value in this array for the value stated in the [Accommodation] column.

It does not matter which order the two XLOOKUP functions are used; however, I have written them in order of row and then column to match R1C1 notation.

Three-Way Lookup

We can combine the techniques of the last two examples to create what some refer to as a three-way lookup.

The second XLOOKUP function is returning the array of values for the matching column like we saw in the two-way lookup example. It is matching the day of week value in cell C2 against the range of values in C4:I4.

The first XLOOKUP then returns the corresponding value in that array for the matching row. The room type and board conditions are met by combining the values from A2 and B2 and A5:A10 and B5:B10. This creates a merged lookup value and lookup array.

All three matches, or conditions, are easily met with these two XLOOKUP functions in a relatively concise formula.

Three-Way Lookup with Mixed Match Modes

Taking the previous example further, the XLOOKUP functions can use different match modes. In Figure 12-17, we have a range containing different room rates that are dependent upon the room type, board, and the time of the year. This range is on a sheet named [Room Rates].

In range C1:F1 are ranges of dates beginning from 10th March 2021. The price of a room changes at different times of the year.

We want to use a three-way lookup to return the room rate for a specified room type, board, and date that the room is booked.

In Figure 12-18, the following formula returns the room rates to range D2:D7. This formula is like the previous example, except in the second XLOOKUP, –1 is specified for the match mode to apply the exact match or next smaller item mode for the date range.

=XLOOKUP(A2&B2,

'Room Rates'!$A$2:$A$7&'Room Rates'!$B$2:$B$7,

XLOOKUP(C2,'Room Rates'!$C$1:$F$1,'Room Rates'!$C$2:$F$7,,-1))

Returning Multiple Values with XLOOKUP

In the last few examples demonstrating two- and three-way lookups, we have seen that an XLOOKUP function can return an array of values for a matching value in a row or column. In each of those examples, the array was passed to another XLOOKUP to return a single matching value.

This array returned by XLOOKUP can, of course, be passed to other Excel functions for use or to the grid itself.

In this example, the values are returned to the grid to be displayed. A defined name could be made for this spill range of values and then used as the data source for a chart like the example at the end of Chapter 10.

Such a simple formula to create a powerful lookup that returns multiple values. The range used for the lookup array and return array could be formatted as a table to fully take advantage of this dynamic array behavior.

In this example, the values are returned in the same order as displayed in the lookup array. This works in this scenario, and because the labels in range C1:G1 are month names, the order is unlikely to change.

Another XLOOKUP is added to the formula to match the values in range C1:G1 to the header labels in range C4:G4. This returns an array with the values in the order that they are matched, as opposed to inferring that the order of the two ranges is the same.

In Figure 12-20, the formula is entered in cell C2. The order of the headers in range C1:G1 has been changed to demonstrate the matching of the header values.

This also provides a more robust formula – one that is unaffected by users changing the order of the values in the lookup array or the lookup values in range C1:G1.

The formula returns a spill range of the resulting values. This is efficient, as it is only one formula returning multiple results.

Instead of returning the values to the grid, they can be returned to another function for use, such as an aggregation function like SUM.

In Figure 12-21, the following formula sums the returned values:

=SUM(XLOOKUP(B2,B5:B10,C5:G10))

XLOOKUP with SUMIFS for a Dynamic Sum Range

The XLOOKUP function can be used with SUMIFS to provide a dynamic column for the sum range. The sum range column can be specified by a cell value. This technique works equally well with AVERAGEIFS, COUNTIFS, etc.

In Figure 12-22, the following formula is entered in cell B4. The XLOOKUP function returns the array of values for the location specified in cell B2. SUMIFS then sums the values in this array for the rows where the values in the [Expense] column match the criteria entered in cell A2.

=SUMIFS(

XLOOKUP(B2,tblExpenses[#Headers],tblExpenses),

tblExpenses[Expense],A2)

To keep the formula as meaningful as possible, XLOOKUP is told to check the entire header row of the [tblExpenses] table, even though only the location columns need checking.

Plus, by referencing the entire header row, if a new location/column is added, the formula continues to work.

XLOOKUP with Other Excel Features

We will conclude the chapter by going beyond the grid and use XLOOKUP with other Excel features. We start with an example of using XLOOKUP to create dependent drop-down lists. This example demonstrates a special technique of using the spill reference appended to the end of a function.

Dependent Drop-Down List with XLOOKUP

A dependent drop-down list is when the items in a list are dependent upon the item selected from a previous list. XLOOKUP provides a neat way for us to set up these dependent drop-down lists in Excel.

Figure 12-23 shows a range that has been prepared for use as the source for the drop-down lists. Dynamic array formulas are used to return the city names for four different countries from a table named [tblCities]. This range is on a sheet named [Lists].

The following formula is entered in cell D1 to return a distinct list of the country names and transpose them to be used as headers:

=TRANSPOSE(UNIQUE(tblCities[Country]))

The following FILTER function is entered in each cell in range D2:G2 to return the cities for the country stated in range D1:G1:

=FILTER(tblCities[City],tblCities[Country]=D1)

Note

The FILTER function is covered in detail in Chapter 13. This is a brilliant function.

We will create a dependent drop-down list so that when a user picks a country from the list, the second list only displays the cities for that selected country.

We want to use the XLOOKUP function to work with the spill ranges returned by the FILTER function. There may be additional cities added or cities removed in the future. By accessing the spill ranges, the drop-down lists would automatically update.

Also, by working directly with the spill ranges, we can avoid the additional blank cells that would appear if we selected the ranges manually. In this example, there are fewer cities in “Spain” in our data than for other countries.

In Figure 12-24, the first drop-down list has been set up in range B2:B4. The following formula is entered for the Data Validation rule. This is a simple reference to the spill range of country names in cell D1 of the [Lists] sheet.

=Lists!$D$1#

We can now create the dependent lists in range C2:C4:

1. 1.

   Select range C2:C4.

    
2. 2.

   Click Data ➤ Data Validation.

    
3. 3.

   Click the Allow list and click List. Enter the following formula into the Source box (Figure 12-25). Click OK.

    

=XLOOKUP(B2,Lists!$D$1:$G$1,Lists!$D$2:$G$2)#

The formula searches for the country specified in the first drop-down along range Lists!$D$1:$G$1. An absolute reference is used as the Data Validation rule is applied to multiple cells in this example.

For the return array, only the first row is referenced: Lists!$D$2:$G$2. These cells contain the spill ranges of the cities. The # sign is appended to the end of the XLOOKUP formula to access the spill ranges.

Note

A name could have been defined for the XLOOKUP formula and then referenced in the Data Validation rule. We have covered this technique previously in this book. It is useful to aid the management of formulas.

Figure 12-26 shows the completed dependent lists. Only the cities for the UK are shown, as that country is specified in cell B4.

This Data Validation rule can be applied to as many cells in columns B and C as required. To simplify the example, the rule is applied to rows 2:4 only. However, we could easily have selected a larger range to apply the Data Validation rules to.

Return a Range for a Chart

The XLOOKUP function can be used to return a range like we saw with the INDEX function in Chapter 11. While INDEX is excellent at relative references and working with index values such as column numbers, XLOOKUP is better for explicit column references and is more succinct for ranges between text values.

Figure 12-27 shows a range of sales values for different products and all 12 months of the year. Range B2:D2 contains input cells to specify a product and the first and last month of a date range.

We want to create a line chart showing the sales for the product specified in cell B2 and for the date range specified by the months in cells C2 and D2.

To do this, we will create two different XLOOKUP formulas: one for the month names to be used in the chart’s X axis and another for the sales values to be used for the chart data series.

Names will be defined for each of the two XLOOKUP formulas and then used for the two elements of the line chart.

Let’s enter the two formulas on the worksheet first. This is not necessary; however, it is good practice, as it provides an opportunity to check that the formulas work correctly, before proceeding to define the names and the subsequent chart steps.

Enter the following formula somewhere on the sheet to return the month names for the specified range in cells C2 and B2:

=XLOOKUP($C$2,$C$4:$N$4,$C$4:$N$4):

XLOOKUP($D$2,$C$4:$N$4,$C$4:$N$4)

Now, enter the following formula to return the sales values for the given product and the specified range in cells C2 and B2 (Figure 12-28):

=XLOOKUP($B$2,$B$5:$B$10,XLOOKUP($C$2,$C$4:$N$4,$C$5:$N$10)):

XLOOKUP($B$2,$B$5:$B$10,XLOOKUP($D$2,$C$4:$N$4,$C$5:$N$10))

With confirmation that the formulas are working accurately, we can proceed to define the names:

1. 1.

   Copy the text for the formula returning the month names.

    
2. 2.

   Click Formulas ➤ Define Name.

    
3. 3.

   Type “MonthLabels” for the Name of the formula.

    
4. 4.

   Paste the formula into the Refers to box (Figure 12-29). Click OK.

    

1. 5.

   Repeat these steps to define a name for the formula that returns the sales values. Name it “MonthValues.”

    

Note

The sheet name is added automatically by Excel to each of the references in the named formulas.

Now, we can insert the line chart and assign the defined names to the axis and data series:

1. 1.

   Click Insert ➤ Insert Line or Area Chart ➤ Line to insert a blank line chart.

    
2. 2.

   With the chart selected, click Chart Design ➤ Select Data. This opens the Select Data Source window (Figure 12-32).

    
3. 3.

   Click the Add button in the Legend Entries (Series) area of the window.

    
4. 4.

   Click in the Series name box and click cell B2 containing the name of the selected product (Figure 12-30).

    
5. 5.

   Remove the text in the Series values box, click a cell on the worksheet to enter the sheet name quickly, delete the cell reference, and type “MonthValues”. Click OK.

    

1. 6.

   Click the Edit button in the Horizontal (Category) Axis Labels area of the window.

    
2. 7.

   Remove the text in the Axis label range box, click a cell on the worksheet to enter the sheet name quickly, delete the cell reference, and type “MonthLabels” (Figure 12-31). Click OK.

    

Note

Instead of typing the name, you can also press F3 to open the Paste Name window, select the name, and click OK.

The completed Select Data Source window is shown in Figure 12-32. You can see the “Waffles” data series and month range correctly picked up.

Figure 12-33 shows the completed line chart. Further formatting can be applied to enhance the chart.

Summary

In this chapter, we learned the new XLOOKUP function – a powerful and robust function, built for modern Excel, with its ability to work with tables, arrays, and dynamic ranges effortlessly.

This was supported by practical examples, including performing multi-criteria lookups and returning dynamic ranges for dependent lists and chart ranges. We also covered the developments that were made to improve on its predecessors VLOOKUP and HLOOKUP.

In the next chapter, we will dive into the FILTER function in Excel – another fantastic function that makes you wonder how we managed without it.

FILTER is another lookup function, one that returns all matching values. We will see numerous examples of its use to showcase its benefits and see how it can be applied in “real-world” scenarios.