You have come to the right place if you are looking for the answer or some unique tips to match multiple criteria from different arrays in Excel. There are various ways to match multiple criteria from different arrays in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.
Watch Video – Match Multiple Criteria from Different Arrays in Excel
How to Match Multiple Criteria from Different Arrays in Excel: 6 Methods
In this section, I will show you 6 quick and easy methods to match multiple criteria from different arrays in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.
In this dataset, I have tried to provide a real-life example. The dataset contains information about some clothing products. It has four columns, the name of the product, the Color, the Size, and the Price as you can see in the following image.
1. Using Array Formula with INDEX and MATCH Functions
Here, I have fetched the Price of the Product (Cell B11) based on the product’s Name, Color, and Size.
📌 Steps:
- For this, first insert the product name, color and size in cells G5, G6, G7
- Then, insert the following formula into cell G8 to get the price for the product meeting those criteria:
=IFERROR(INDEX(E5:E20,MATCH(1,(G5=B5:B20)*(G6=C5:C20)*(G7=D5:D20),0)),"No Match")
🔎 Formula Breakdown:
✅ The Multiplication Operation:
→ (G5=B5:B20)*(G6=C5:C20)*(G7=D5:D20) = (Shirt = Product Column)*(Indigo = Color Column)*(L = Size Column) = {FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}*(G6=C5:C20)*(G7=D5:D20)}
It will search the values to the respective column and return TRUE/FALSE values according to it.
→ {0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0}
The Multiplication Operator (*) converts these values to 0s and 1s and then performs the multiplication operation which converts all other values to 0s except the desired output.
✅ MATCH Function Operation:
→ MATCH(1,(0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0),0)) → 13
This function looks for the value 1 in the converted range and returns the position.
✅ INDEX Function Operation:
→ IFERROR(INDEX(E5:E20,13), “No Match”) → 50
This function returns the value in the 13th row of the price column which is the desired output. For cases where there are no matches, the INDEX function will return a #N/A error. For handling such errors and displaying a human-readable message, “No Match“, the IFERROR function is used here.
2. Using a Non-Array Formula of INDEX and MATCH Functions
Here, I have tried to perform the same task as before. The formula is also the same except there is an extra INDEX function and a selected non-array type of the INDEX function.
📌 Steps:
- For this, first insert the product name, color, and size in respective cells.
- Then, insert the following formula into cell G8 to get the price for the product that meets those criteria:
=IFERROR(INDEX(E5:E25,MATCH(1,INDEX((G5=B5:B25)*(G6=C5:C25)*(G7=D5:D25),0,1),0)),"No Match")
🔎 Formula Explanation:
The main purpose of this new INDEX function is to convert the previous array formula to a non-array formula so that it can be implemented by someone who is not familiar with Excel array functions. The new INDEX function handles the returned array after the multiplication operation eliminating the need for an array formula.
3. INDEX MATCH Formula for Multiple Criteria from Different Horizontal and Vertical Arrays in Excel
3.1 Lookup Vertically in Columns
Apart from the previous ways described above, you can combine INDEX and MATCH functions to lookup for horizontal and vertical lookup with multiple criteria. Follow the steps below to do this.
📌 Steps:
- Initially, click on cell C18 and insert the following formula.
=INDEX(D5:D14,MATCH(1,(B5:B14=C16)*(C5:C14=C17),0))
- Subsequently, hit the Enter key.
As a result, you can get the desired result for your desired salesperson.
3.2 Lookup Horizontally in Rows
You can also combine the INDEX and MATCH functions to look up values horizontally with multiple criteria by following the steps below.
📌 Steps:
- First, click on cell C10.
- Subsequently, insert the following formula and press the Enter
=INDEX(C6:L6,MATCH(1,(C4:L4=C8)*(C5:L5=C9),0))
Thus, you can get the desired person’s department by horizontal lookup.
4. INDEX MATCH Formula to Match Multiple Criteria from Arrays in Different Excel Sheets
Consider a situation where you are working on a business farm. Your boss gave you an assignment in which you have to find the sales amount of different sales reps from another worksheet. You can do it easily by using the INDEX MATCH formula with multiple criteria.
- In the following example, the “ID”, “First Name”, and “Sale” of workers are given arbitrarily. You have to find the “Sale” for a specific “ID” and a specific “First Name” in a different worksheet. The worksheet is named “Data”.
- Make another table in a new worksheet containing columns “ID”, “First Name”, and “Sale”. In this new worksheet, we will find the result. Name this worksheet as “M01”.
- Then, insert the following formula in cell D5 of “M01” worksheet.
=INDEX(Data!$D$5:$D$15,MATCH(1,('M01'!B5=Data!$B$5:$B$15)*('M01'!C5=Data!$C$5:$C$15),0))
- Now, apply the same formula for the rest of the cells.
- Thus, you have used multiple criteria to find out a value from different worksheets.
5. Using the COUNTIFS Function to Match Multiple Criteria from Different Arrays
We can use the COUNTIFS function to match multiple criteria in rows and columns in Excel. In that case, we have to use the AND logic for criteria in different columns and OR logic for criteria in the same column.
5.1 Using AND Logic for Multiple Criteria in Multiple Columns
AND logic means that all criteria should be matched to get the true value. Here, I have calculated the total number of rows based on the Name, Color, and Size criteria.
📌 Steps:
- First, insert the name of the product, color, and size in respective cells of range F5:F7.
- Then, insert the following formula in cell F8 to get the count of the cells that match the given criteria:
=COUNTIFS(B5:B20,F5,C5:C20,F6,D5:D20,F7)
🔎 Formula Breakdown:
=COUNTIFS(B5:B20,F5,C5:C20,F6,D5:D20,F7) → COUNTIFS(Product Column, Shirt, Color Column, Indigo, Size Column, L) → 1
- It searches for the values in the respective columns and increases the count if all the criteria are matched.
- There is only one column where all the criteria match. So, it is the desired output.
- Thus, you have the cells count matching the criteria given for different arrays.
5.2 OR Logic for Multiple Criteria in the Same Column
OR logic means that if one criterion matches, the TRUE value will be returned. Here, I have calculated the total number of rows where the color values are “Red” and “Yellow”.
To do that, insert the following formula in cell F4 to get the count of the cells that match the given criteria:
=SUM(COUNTIFS(C5:C20,{"Red","Yellow"}))
🔎 Formula Breakdown:
→ SUM(COUNTIFS(C11:C31,{“Red”,“Yellow”})) → SUM(COUNTIFS(Color column,{“Red”, ”Yellow”}))
COUNTIFS function searches for the values in the respective column and increases the count if any criteria are matched. As there are three “Red” and three “Yellow”, that’s why the COUNTIFS function returns 3,3.
→ SUM(3,3) → 6
The SUM function adds the two values and returns the desired output.
- Thus I have calculated total amount of red and yellow products.
6. Using the FILTER Function
As the name suggests, the FILTER function filters a range of cells based on certain criteria. You don’t write formulas with multiple functions for this method. Only the FILTER function is enough to perform the operation. Here, I have fetched the Price of the Product (Cell B11) based on the product’s Name, Color, and Size.
📌 Steps:
- First, insert the name of the product, color, and size in respective cells of range F5:F7.
- Then, insert the following formula in cell F8 to get the price of the product that matches all the criteria.
=FILTER(E5:E20,(B5:B20=G5)*(C5:C20=G6)*(D5:D20=G7),"No Match")
🔎 Formula Breakdown:
✅ The Multiplication Operation:
→ (B5:B20=G5)*(C5:C20=G6)*(D5:D20=G7) = (Product Column = Shirt)*(Color Column = Indigo)*(Size Column = L) = {FALSE ;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}*(C5:C20=G6)*(D5:D20=G7)}
It will search the values to the respective column and return TRUE/FALSE values according to it.
→ {0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0}
The Multiplication Operator (*) converts these values to 0s and 1s and then performs the multiplication operation which converts all other values to 0s except the desired output.
✅ The FILTER Function:
→ FILTER(E14:E34,{0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0},”No Match”) = FILTER(PriceColumn {0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0; 0;0;0},”No Match”) = 50
The FILTER function searches the Price column with index numbers and returns the cell value where the corresponding index value is one (1), In this case, 50.
- Thus, I have calculated the price of a product that matches the multiple criteria given.
Note:
As of writing this article, the FILTER function is only available on Excel 365. So, if you are using other versions of Excel then you should check the other methods.
You can download the workbook that I used in this article from below and practice with it by yourself.
Conclusion
In this article, you have found how to match multiple criteria from different arrays in Excel. I hope you were able to find a solution to your problem. Please leave a comment if you have any suggestions or questions. Thank you.
<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
him thanks for the wonderful tips! The formula keeps showing that there’s no match “1”.I’ve tried many different formula and it keeps showing the same warning. Could you please assist me? much appreciated!!
Hello Avery,
First of all, we would like to thank you for your appreciation. Again, thanks for taking the time to leave a comment about a problem that you’re facing. In our Excel file, the formula is working smoothly. You can follow the step-wise procedure thoroughly. If the problem in your file remains the same, then send the file to us. It will be easier for us to pinpoint the problem. We, the Exceldemy team, are always ready to solve users’ issues.
Regards,
Fahim Shahriyar Dipto
Excel and VBA Content Developer