INDEX MATCH with 3 Criteria in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

To extract data with different criteria or conditions in Microsoft Excel, the combination of INDEX and MATCH functions is best suited so far. In this article, you’ll get to learn how you can use these INDEX and MATCH functions together with 3 different criteria in Excel with proper illustrations.

INDEX MATCH with 3 Criteria in Excel: 4 Uses

1. INDEX MATCH with 3 Criteria in Excel (Array Formula)

In the following dataset, a number of Xiaomi smartphone models are lying with corresponding chipset models, RAM, and Camera configurations. Based on the data available in the table, we’ll find out 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.

index match with 3 criteria in excel array formula

Select the output Cell E17 and type:


Now press CTRL+Shift+Enter to find the output as this is an Array formula. But if you’re using Microsoft 365 then you have to press Enter only.

index match with 3 criteria in excel array formula

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.

Read More: How to Use INDEX MATCH with Multiple Criteria in Excel

2. INDEX MATCH with 3 Criteria in Excel (Non-Array Formula)

If you don’t want to use an array formula, then here’s another formula to apply in the output Cell E17:


After pressing Enter, you’ll get similar output as found in the previous section.

index match with 3 criteria in excel non-array formula

🔎 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:


  • 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.

Read More: Excel INDEX MATCH with Multiple Criteria and Multiple Results

3. Combination of IFERROR, INDEX, and MATCH Functions with 3 Criteria

Sometimes, the given criteria or conditions may not match the data available in the table. In that case, any of the formulas in the last two methods will return a #N/A error. But we can modify the formula to return a “Not Available” message if the given criteria are not matched. So, we have to use the IFERROR function to serve the purpose.

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.

index match with iferror function 3 criteria in excel

4. INDEX MATCH with 3 Criteria along Column(s) and Row(s) in Excel

In the final section, we’ll now 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.

index match with 3 criteria along column and row headers in excel

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.

index match with 3 criteria along column and row headers in excel

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.

Download Practice Workbook

You can download the Excel workbook that we have used to prepare this article.

Concluding Words

I hope all of these methods mentioned above will now help you to apply them in your Excel spreadsheet while working with INDEX and MATCH functions with 3 different criteria. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

  1. Simply masterpiece

    • Dear Forte,

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

      Shamima | Project Manager | ExcelDemy

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


    • 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.


Leave a reply

Advanced Excel Exercises with Solutions PDF