Free Online Services Provider: INDEX and MATCH Functions with multiple criteria in Microsoft Excel

Brief Introduction

To lookup values with INDEX and MATCH Functions while using multiple criteria, we need to use an array formula. This is a more advanced formula. For basics, see How to use INDEX and MATCH Functions in Microsoft Excel.

The INDEX and MATCH Functions combo is potent and flexible, and you'll see it used in all kinds of formulas, from basic to very advanced. Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Ms. Excel for performing lookups.

However, while VLOOKUP allows you to perform lookups with a single function, INDEX and MATCH requires two functions, one nested inside another. Many users find this confusing, because they aren't used to combining functions in Ms. Excel, so they avoid INDEX and MATCH. That's a shame. Combining functions is the key to more advanced formulas in Excel.

Previously I already have shared a post about Index & Match Function see it on my blog while now I want to Share with you a post about to lookup values with INDEX and MATCH Functions while using multiple criteria which is very advanced Function in Microsoft Excel.

Purpose:

To lookup values with INDEX and MATCH function during using the multiple criteria.

Syntax of INDEX & MATCH Functions:

The syntax of Index & Match Functions in Excel are as follows;

=INDEX(array, row_num, [column_num])

=INDEX(reference, row_num, [column_num], [area_num])

=MATCH(lookup_value, lookup_array, [match_type])

Return value:

The Lookup Value which meets the multiple criteria.

Arguments:

Range - The range of cells on which we want to apply the criteria against.

Criteria - The criteria (Condition) used to determine which cells to lookup.

You can download free the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below download icon,

This article explains in simple terms how to use Index & Match Functions during multiple criteria. It takes a step-by-step approach, & following below contents we will touch in this post.

Index & Match Functions combo during multiple criteria explanation through various examples.

Only Match Function during multiple criteria.

Let’s start to learn something new about the Index & Match Functions of Microsoft Excel during Multiple Criteria.

1. Index & Match Functions combo during multiple criteria - Example 1.

Index & Match Functions are used to lookup value in a range of cells based on multiple criteria, as we explain this function through a given below example,

Explanation: - In the example, we have taken the information about a hospital, from "Row 2" to "Row 9" which contains the information of staff Names, their position, location, budget code (through which their salaries paid) & monthly salaries & from "Row 11" to "Row 13" which contains the criteria & Function result. As we see that the cell "F12" contains the formula & "B12,C12,D12,E12" are our criteria when these criteria we apply on the above-given information, we find out that our criteria match with above-given information & "Row 5" contains our exact answer $ 4,890/-.

Note

When we complete our formula & before exiting from it, we need to press "Ctrl + Shift + Enter" instead of the "Enter key", because "Ctrl + Shift + Enter" will enclose our formula in curly brackets. As shown in the above example.