At the core, this is an approximate match lookup based on weight.
The challenge is that we also need to filter by service.
This works because the IF function returns 12 results, which map to the 12 rows in the table.
See below for more details.
Background reading
This article assumes you are familiar with Excel Tables and INDEX and MATCH.
If you are new to INDEX and MATCH,see this overview.
We have a simple working formula that returns the correct cost based on an approximate match lookup.
The complication is that we also need to match based on Service.
To do that, we need to extend the formula to handle multiple criteria.
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.
Remember that we are using MATCH in an approximate match mode.
See ourMATCH function pagefor more information.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
More than one condition can be tested by nesting IF functions.
