INDEX MATCH with 3 Criteria in Excel (4 Examples)

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.

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.

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.

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

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:

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.

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.

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

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.

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

• INDEX MATCH Multiple Criteria with Wildcard in Excel
• INDEX MATCH Formula with Multiple Criteria in Different Sheet
• INDEX MATCH across Multiple Sheets in Excel
• INDEX-MATCH with Duplicate Values in Excel
• How to Use INDEX-MATCH Function for Multiple Results in Excel
• Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
• INDEX Function to Match & Return Multiple Values Vertically in Excel