## What Is INDEX MATCH With Multiple Criteria

The

INDEX MATCH functioncombination is an alternative to theVLOOKUP function. TheINDEX MATCH With Multiple Criteriahelps users to retrieve the required data in a dataset where we may not find a unique column to apply the lookup function. Therefore, in such scenarios, we find a pattern to identify the uniquelookup_valuecolumn, and the multiple criteria columns to create a unique column to apply the lookup logic to retrieve values.

##### Table of contents

###### Key Takeaways

**INDEX and MATCH functions**can match multiple criteria with the helper column to create a unique column, and can also be used as nested functions to match multiple criteria.- They retrieve the values from right to left, and vice versa, and are often used as an alternative to the
**VLOOKUP function.** - Two
**MATCH function**s can be used for a two-way lookup of values when the column header changes. - The combination of the
**VLOOKUP**and**CHOOSE functions**can be used as an alternative to**INDEX and MATCH with multiple criteria**.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### How To Use INDEX MATCH With Multiple Criteria?

First, we will understand what is meant by multiple criteria in the lookup function.

Consider the following data in Excel. We have product sales with different box types.

**Step 1: **First, let us apply the **INDEX MATCH function**, to retrieve the sales figure for the product “**Orange**”.

We have got the result as **289**. But, let us look at the data closely to find the real problem.

We have the product “**Orange**” appearing 3 times with different box sizes. That means the product is not a unique column to retrieve the value. **INDEX MATCH function** retrieved the value from the first matching record, and the first matching record quantity is **289**.

When we try to retrieve the quantity value for the product, we must be sure which box type we are looking for. The “P**roduct”** is not the only criteria to retrieve the value, we must include the “**Box Type**” as the second criteria to find the quantity number for the specific box type and product.

**Step 2: **Let us modify the result table as follows.

There is another problem here, i.e., we do not have the unique column yet, to apply the **INDEX MATCH multiple criteria**.

**Step 3: **First, we must create a unique column in the combination of “**Product**” and “**Box Type**”. Insert a new column called “**Helper**”, and apply the concatenation formula to combine the **“Product” **and** “Box Type”** columns with the pipe symbol **“|”** as the delimiter between these columns.

Now that we have the unique column in place, let us apply the **INDEX and MATCH functions** to retrieve the quantity for the specific product and box type.

**Step 4:** Enter the **INDEX function** in cell **I2**.

**Step 5:** Choose the array as the result column, i.e., the Quantity column from **D2:D10**.

**Step 6: **Next, we must find the row number for the combination of the product “**Orange**” and box type “**Large**”. To find it dynamically, we will use the **MATCH function** in the row number argument of the **INDEX excel function**.

Now, we must choose the ** lookup_value** for which we must find the row number. However, we do not have a single

**here, but we have two.**

*lookup_value***Step 7: **We have created a unique column in the table by combining the **Product **and** Box Type**. Let’s combine these two columns in the ** lookup_value** in the same manner we have created the

**column.**

*lookup_value***Step 8: **Choose the lookup array from **C2:C10**.

**Step 9:** Enter the matching type as 0 to get the exact match.

**Step 10: **Close the brackets of both the **INDEX and MATCH functions** and hit the enter key to get the result.

Now the **INDEX and MATCH formula** retrieves the correct value. We can change the **Product Name** in cell **G2**, and **Box Type** in cell **H2**, to get the corresponding quantity value.

For instance, look at the following image.

The formula returns the quantity value for the product “**Grape**”, and box type “**Small**”.

### Advanced Examples

We will consider some advanced examples of **INDEX MATCH multiple criteria**, namely,

**Two INDEX functions and a MATCH function with Multiple Criteria.****Using INDEX with Two MATCH functions with Multiple Criteria in Excel.**

#### Example #1

**Two INDEX functions and a MATCH function with Multiple Criteria**

In the above example, we have used the helper column to get the quantity value. Now, let us understand retrieving the values without the helper column.

We are using the same data from the previous example. Here, the challenge is to find the row number for multiple criteria like product and box type.

First, let us understand how to find the row number for the given **product **and** box type.**

Enter the **MATCH function** in cell **H2**.

Choose the ** lookup_value** as cells

**F2**and

**G2**with the ampersand symbol to concatenate together.

This combination shows that we are trying to find the row number for these two matching records in the table.

Next, to choose the lookup array, we do not have the helper column. Hence, enter the **INDEX function** to choose the lookup array.

First, choose the product array from **A2:A10**.

Then enter the ampersand (&) symbol and choose the box type array from **B2:B10**.

Enter a comma (,), and close the bracket of the **INDEX function**.

For the **MATCH function**s, the last argument enters 0 to get the exact match.

Close the bracket and press the “**Enter**” key to get the row number for the given combination of product and box type.

It returns the row number of the position of the given combination of the product and box type, i.e., **8**.

We can combine this with another **INDEX function** to get the quantity value like the following.

Here the first **INDEX function** looks for the quantity value in the range **C2:C10** based on the row number given by the **MATCH and INDEX** combination formula.

#### Example #2

**Using INDEX with Two MATCH functions with Multiple Criteria in Excel**

Continuing the previous example, let us retrieve the values from different columns.

We have added another column, i.e., the “**Sales**” column, to the previous sales table. Users will change the column name in cell **H2** to see either **Quantity **or** Sales**.

So, based on the changes done by the users, our formula should retrieve the value from the given column header name. To do this dynamically, we must include the **MATCH function** to get the column number for the column header given by the users.

We will use the previous formula, and remove the last bracket to find the column number.

Change the first index range from **C2:C10 **to** C2:D10.**

Now, to fetch the column number for the column header, enter the **MATCH function**.

Choose the ** lookup_value** as cell

**H1**.

Choose the table array from **C1:D1.**

Enter the matching type as an exact match, and close the bracket.

The formula returns the sales value because in cell **H1,** we have given the column header as “**Sales**”. Change the column name to “**Quantity**” to get the result from the quantity column.

Now the formula returns the values from the **Quantity** column.

### Alternatives To INDEX MATCH Multiple Criteria

There are various alternatives to the **INDEX MATCH multiple criteria**. One such alternative is the combination formula of **VLOOKUP and CHOOSE Excel functions**.

Let us take the same data from the earlier example here as well.

First, let us understand how the **CHOOSE function** works.

For the **CHOOSE function**, we must enter the index number, and then it will choose the value from the respective position from the values given.

For instance, look at the following example.

In the Index argument, we have given 2, and in the values arguments, we have given days of the week.

There are 7 values (days) given, and the **CHOOSE function** chooses the value from the second position of the sequence of the values, i.e., “**Tue**”.

Similarly, we can combine all the ** lookup_value** columns from the table, like the following.

In the index number argument of the **CHOOSE function**, we have given 1 and 2 in the curly bracket, i.e., when we must select multiple values from multiple positions, we can use the curly bracket and mention the index numbers.

Then in the **value1** argument, we have combined all the criteria columns, i.e., **“Mobile Brand”, “Version”, **and** “Month” columns.**

Then in the **value2 **argument, we have given reference to the “**Units**” column. Hence, the **CHOOSE function** returns the table, as shown below.

We can use the same formula in the table array argument of the **VLOOKUP Excel function** to avoid creating the helper column.

In the above formula, the **CHOOSE function** returns the table array as the concatenation of ** lookup_value** columns and unit columns. Then, the

**VLOOKUP function**retrieves the value from the table array given by the

**CHOOSE function**.

### Important Things To Note

- If we use Excel versions other than Office 365, we must execute all the formulas as an array formula using the keys
**Ctrl + Shift + Enter**. - The combination of
**VLOOKUP and CHOOSE**is also an array formula in all the Excel versions apart from Office 365. - The helper column method increases the size of the Excel workbook if you are working with a large data set.

### Frequently Asked Questions (FAQs)

**1. How do INDEX MATCH multiple criteria across multiple columns?**

To apply **INDEX MATCH multiple criteria** across multiple columns, we must use the two-way lookup by including two **MATCH function**s inside the **INDEX function**.

For instance, look at the following example.

When the user changes the column header in cell **J1**, the formula will return either the Units or the Sales Value column result.

**2. Why is the index match with multiple criteria used?**

**INDEX MATCH multiple criteria** are used to overcome the limitation of duplicate values in a single column by combining two or more criteria columns.

**3. What are some limitations of using INDEX MATCH multiple criteria?**

Some of the limitations of using** INDEX MATCH multiple criteria are,****• **If you are using Excel versions other than Office 365 versions, then you must execute the formulas as an array formula by using the **Ctrl + Shift + Enter** keys.**• **Adding a helper column will add unnecessary data to the workbook and increase the size of the dataset.

**4. What INDEX MATCH multiple criteria does?**

The **INDEX and MATCH multiple criteria** retrieve the value based on more than one column. Hence, it matches multiple criteria from the table and retrieves the corresponding value.

### Download Template

This article must help understand **INDEX MATCH multiple criteria** with its formulas and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to INDEX MATCH Multiple Criteria. Here we find lookup value with INDEX-MATCH, VLOOKUP-CHOOSE, examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply