Hi - Dave here.
Happy Friday!
A tricky problem in Excel is how to configure INDEX and MATCH to apply multiple criteria and also perform an approximate match.
For example, in the worksheet below, we want to lookup the correct cost based on the (1) Service and (2) Weight. Service is an exact match condition. But weight is approximate match. How best to combine these two conditions?
One classic solution is to use the IF function to "filter out" weights for other services, then use the MATCH function to find the right weight with a normal approximate match. This is the approach used below, where the formula in G8 is:
=INDEX(data[Cost],MATCH(G7,IF(data[Service]=G6,data[Weight]),1))
[
Download the workbook and read the article]
One reason this works is that the IF function returns 12 results, which map to the 12 rows in the table. This means MATCH will still return the correct row in the table even after IF has filtered out the other weights. The article explains all the details. Download the workbook and try it out yourself.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
