The challenge is that we also need to filter by service.
This means XLOOKUP can return the right value in the table because all 12 rows are still intact.
You could instead use theFILTER 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:
Basic XLOOKUP
In XLOOKUP formulas, alookup_arrayandreturn_arrayare provided as arguments.
If you are new to XLOOKUP,this short videoshows a basic example.
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 useBoolean logic,as explained here.
The classic way to do this is with theIF function.
Where there is a match, the corresponding values in with Weight column are returned.
If there is no match, the IF function returns FALSE.
Notice that we are using -1 inside XLOOKUP as thematch_modeargument.
This will cause XLOOKUP to match a value that is less than or equal to the lookup value.
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.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
More than one condition can be tested by nesting IF functions.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
