How to Find Approximate Match with Multiple Criteria in Excel

Microsoft Excel features a series of complicated formula and equations, which can implement for simplifying a problem. It also features INDEX and MATCH functions for Lookup & Reference purposes. When these two functions are combined together logically, it acts in combination to find an approximate match with multiple criteria.  Let’s have a look at the following guide for better clarification:

Finding Approximate Match with Multiple Criteria in Excel:

Figure1. Example of how to use INDEX/MATCH for finding out an approximate match with multiple criteria

Formula:

=INDEX(D5:D10,MATCH(G7,IF(B5:B10=G6,C5:C10),1))

Explanation of formula:

In certain cases, an Excel user is often required to find out an approximate match with respect to multiple criteria. It requires the user to specify the stipulation of the items. For example, let’s have a look at the screenshot attached above; a user is required to specify the size of the cat, and it is given a range of weight options in the array. Using the formula highlighted above as well as in Excel’s formula bar, it will be able to return value to the selected cell (G7).

The main objective of using the formula is based on looking up and approximates a corresponding match on the basis of its given criteria. A user can use array formula using MATCH and INDEX; however, it should also integrate IF function into the formula for specifying its conditional format.

How does the formula work?

The equation is a combination of INDEX and MATCH, which is further supplemented by a conditional IF function. The addition of {} to the equation converts it into an array formula, which should be maneuvered using CSE (Control +Shift+ Enter).

Also, once you have entered the formula, you can manually change its values, and it would return the values accordingly. Let’s have a look at the following example:

Figure2. Example of changing the specifications to find out the approximate match with multiple criteria

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar