XLOOKUP approximate match with multiple criteria

In this example, the goal is to look up the correct shipping cost for an item based on the shipping service selected and the weight of the item. The challenge is that we also need to filter by service. This means we need to apply criteria in two steps: (1) match based on Service and (2) match based on Weight. The screen below shows the basic idea:

One way to solve this problem is with XLOOKUP + the IF function to perform the required filtering. One reason this works nicely is that the IF function returns 12 results, which correspond to the 12 rows in the table. This means XLOOKUP can return the right value in the table because all 12 rows are still intact. You could instead use the FILTER function, with a bit more configuration. See below for details.

Background reading

This article assumes you are familiar with Excel Tables and XLOOKUP. If not, see:

• Excel Tables - introduction and overview
• XLOOKUP function - overview with examples and videos

Basic XLOOKUP

In XLOOKUP formulas, a lookup_array and return_array are provided as arguments. XLOOKUP locates the lookup value in the lookup array, then it returns the corresponding value in the return array. If you are new to XLOOKUP, this short video shows a basic example.

Looking at this problem from the inside out, the core of the solution is an approximate match lookup based on weight. To illustrate, the screen below shows a simplified version of the same problem with the Service removed:

The formula in cell F5 is:

=XLOOKUP(F4,B5:B8,C5:C8,,-1)

The lookup_array is the weight in the range B5:B8, and the return_array is the cost in the range C5:C8. Notice the match_mode argument inside XLOOKUP is set to -1, to find the largest value in B5:B8 that is less than or equal to the lookup value in cell F4. In this case, the largest value less than or equal to 72 is 60, so XLOOKUP matches the 60 and returns $18.00 as a final result:

=INDEX(C5:C8,3) // returns 18

So far, so good. We have a simple working XLOOKUP formula that returns the correct cost based on an approximate match lookup. The challenge is that we also need to match based on Service. To do that, we need to extend the formula to handle another condition.

Adding criteria for service

We know how to look up costs based on weight. The remaining challenge is that we also need to take into account Service. For simple exact-match scenarios, we can use Boolean logic, as explained here. But in this example, we need to perform an approximate match, so using Boolean logic will not work well. Another approach is to "filter out" extraneous entries in the table so we are left only with entries that correspond to the Service we are looking up. The classic way to do this is with the IF function. This is the approach used in the example shown, where the formula in cell G8 is:

=XLOOKUP(G7,IF(data[Service]=G6,data[Weight]),data[Cost],,-1)

The filtering is done with the IF function, which appears inside the XLOOKUP function like this:

IF(data[Service]=G6,data[Weight])

This code tests the values in the Service column to see if they match the value in G6. Where there is a match, the corresponding values in with Weight column are returned. If there is no match, the IF function returns FALSE. Because there are 12 rows in the table, the IF function returns an array that contains 12 results like this:

{FALSE;FALSE;FALSE;FALSE;1;16;60;120;FALSE;FALSE;FALSE;FALSE}

Notice the only weights that remain in the array are those that correspond to the "2-Day-Air" service; all other weights have been replaced with FALSE. You can visualize this operation in the original data as shown below:

This array is delivered directly to XLOOKUP as the lookup_array:

=XLOOKUP(G7,{FALSE;FALSE;FALSE;FALSE;1;16;60;120;FALSE;FALSE;FALSE;FALSE},data[Cost],,-1)

With a weight of 72 in cell G7, XLOOKUP matches 60 and returns $45.00 as the final result. Notice that we are using -1 inside XLOOKUP as the match_mode argument. This will cause XLOOKUP to match a value that is less than or equal to the lookup value.

XLOOKUP with FILTER

Another way to solve this problem is with XLOOKUP and the FILTER function like this:

=XLOOKUP(G7,FILTER(data[Weight],data[Service]=G6),FILTER(data[Cost],data[Service]=G6),,-1)

In this formula, we are removing data for other services with the FILTER function. Inside XLOOKUP, FILTER creates the lookup_array like this:

FILTER(data[Weight],data[Service]=G6) // returns {1;16;60;120}

The return_array is created in the same way:

FILTER(data[Cost],data[Service]=G6) // returns {22.5;30;45;60}

After FILTER runs, XLOOKUP is only working with values associated with the 2-Day air service:

=XLOOKUP(G7,{1;16;60;120},{22.5;30;45;60},,-1)

With 72 in cell G7, XLOOKUP returns the same result as before, a cost of $45.00. This formula works nicely and is perhaps more intuitive than XLOOKUP + IF. However, the tradeoff is a more complex formula since FILTER must be used twice.