Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles LOOKUP How to Find Approximate Match with Multiple Criteria in Excel

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. 

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

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc