INDEX and MATCH multiple criteria + approximate match

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)) [INDEX and MATCH multiple criteria + approximate match]( [[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 [View email in browser]( [Exceljet Logo]( Exceljet P.O. Box 4804 Salt Lake City, UT 84110 Copyright © 2023 Exceljet, All rights reserved. You received this email because you are subscribed to our newsletter. To unsubscribe, click the link below. [Unsubscribe](