Go Back

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



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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Index/Match formula with multiple criteria
Solved by T. S. in 27 mins
I need help with an INDEX(MATCH()) using multiple criteria
Solved by M. E. in 20 mins
i need help with an index match formula which has to match a criteria across multiple columns
Solved by Z. Y. in 30 mins

Leave a Comment