# INDEX MATCH with 3 Criteria in Excel (4 Examples)

### Method 1 – INDEX MATCH with 3 Criteria in Excel (Array Formula)

The following dataset contains a number of Xiaomi smartphone models with corresponding chipset models, RAM, and Camera specs. Based on the data available in the table, we’ll find a smartphone model that meets three different criteria from the first three specifications columns. For example, we want to find out a model that uses a Snapdragon chipset, has 8 GB RAM, and has a 108 MP camera, which are listed in a smaller table below.

• Select the output Cell E17 and type:
`=INDEX(E5:E14,MATCH(1,(C17=B5:B14)*(C18=C5:C14)*(C19=D5:D14),0))`
• Press CTRL + Shift + Enter to find the output as this is an Array formula. If you’re using Microsoft 365, then you have to press Enter only.

Here, the MATCH function extracts the row number based on the defined criteria. With its first argument as 1, the MATCH function looks for the value 1 in the lookup array (second argument) where all criteria have been met and it returns the corresponding row number. INDEX function then uses this row number to extract the smartphone model from Column E.

### Method 2 – INDEX MATCH with 3 Criteria in Excel (Non-Array Formula)

• Here’s another formula you can apply in the output Cell E17:
`=INDEX(E5:E14,MATCH(1,INDEX((C17=B5:B14)*(C18=C5:C14)*(C19=D5:D14),0,1),0))`
• After pressing Enter, you’ll get similar output as found in the previous section.

How Does the Formula Work?

• Inside the formula, the second argument of the MATCH function has been defined by another INDEX function which looks for multiple matched criteria and returns an array:

{0;1;0;0;0;1;0;0;1;0}

• MATCH function then looks for the value- 1 in this array and returns the corresponding row number of the first finding.
• Finally, the outer INDEX function extracts value from Column E based on the row number found in the preceding step.

### Method 3 – Combination of IFERROR, INDEX, and MATCH Functions with 3 Criteria

We can modify the formula to return a “Not Available” message if the given criteria are not matched.

The required formula in the output Cell E17 should be now:

`=IFERROR(INDEX(E5:E14,MATCH(1,INDEX((C17=B5:B14)*(C18=C5:C14)*(C19=D5:D14),0,1),0)),"Not Available")`

After pressing Enter, we’ll see the defined message- “Not Available” as we have modified the criteria a bit that are unable to correlate with the data available in the table.

### Method 4 – INDEX MATCH with 3 Criteria along Column(s) and Row(s) in Excel

In the final section, we’ll assign Chipset and RAM headers in two separate rows (4 and 5). We have also added two more smartphone brands in Column C. The range of cells from D6 to F8 represent the corresponding models based on the brands, chipsets, and RAMs across the column and row headers.

Based on this matrix lookup with multiple criteria along rows and columns, we’ll pull out the smartphone model in Cell E11 that meets the criteria defined in the range of cells D11:D13.

In the output Cell E11, the required formula under the specified conditions will be:

`=INDEX(D6:F8, MATCH(C13,C6:C8,0), MATCH(C11&C12,D4:F4&D5:F5,0))`

After pressing Enter, you’ll find the final output as shown in the screenshot below.

In this formula, the first MATCH function defines the row number from Column C that matches the given criteria for brands. In the third argument (column_num) of the INDEX function, the second MATCH function defines the column number by combining the chipset and RAM criteria.

<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

1. Simply masterpiece

2. Thank You!

• Dear Forte,

Thanks for your appreciation. Stay in touch with ExcelDemy to get more helpful content.

Regards
Shamima | Project Manager | ExcelDemy

3. How do I convert the below formula into a non- array formula?

Thanks

• Hello JK,

Which formula are you talking about?
Do you have a formula for yourself that you want to convert?
Or, Do you want to convert any of the formulas from the post?

Please let us know. We would be happy to help.

Regards
Exceldemy

Advanced Excel Exercises with Solutions PDF