Tanzim
Excel is a useful tool for matching multiple criteria from different arrays. Matching criteria is one of the most common things that people do with Excel every day. You can match multiple different criteria from a range of different arrays using different ways. We’ll show you how to match multiple different criteria from different arrays in Excel today with examples. I’m making examples and workbooks with Microsoft 365. You can also use the version you prefer.
📁 Download Excel File
To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.
Learn to Match Multiple Criteria from Different Arrays in Excel with These 6 Unique Methods
Let’s get to the main point of the article. In this tutorial, I’ll show you six handy ways to match using multiple criteria from many different arrays in Excel. Everything is explained in detail and shown with clear pictures in this article. I’ve been using Microsoft 365 here. But you can use other versions if you like. I tried to give easy examples within this dataset. There is information in the dataset about some items and their sales. As we can observe in the image below, it has columns with headings: Region, Month, Item, and Sales. Using the information on the region, month, and item, we will find the sales.
1. Using MATCH and INDEX Functions Without Array Formula
Here, I got the sales of the item by using the region, month, and item. We will use both the INDEX and MATCH. Within a given table or range, INDEX function returns either a value or a reference to a value. The MATCH function looks for a given item in a range of cells and then returns the relative position of that item within the range.
⬇️⬇️ STEPS ⬇️⬇️
- First, put the region, month, and item in the respective cells.
- Now put this formula in cell H9 to know about prices for the item that meets these criteria:
- Then we press Enter. We will get the result in cell H9.
🔨 Formula Breakdown
👉 The operation of multiplication will look for the values in each column and either return TRUE or FALSE based on what it finds. The asterisk which is a Multiplication Operator (*) changes these values into only 0 and 1. After that, it does the multiplication, which changes all values except the one you want into 0.👉 This function searches the converted range for 1 (the value) and returns which position the value is in.👉 This function gives back values in the 9th row of price column, which in turn, is the output that was asked for. INDEX function now will return the #N/A error if there are no matches. We used IFERROR here to handle these kinds of errors and show the message “No Match.”
2. Using MATCH and INDEX Functions with an Array Formula
Here, I got the sales of the item by using the region, month, and product. We will use both INDEX and MATCH here. Within a given table or range, INDEX function usually return either a value or a reference to a value. The MATCH function looks for a given item in a range of cells and then returns the relative position of that item within the range.
⬇️⬇️ STEPS ⬇️⬇️
- First, put the region, month, and item in the respective cells.
- After that, type the formula inside cell H9 to get price of the product that meets the criteria we desire:
- Then we press Enter. We will get the result in cell H9.
🔨 Formula Breakdown
👉 The special multiplication operation will look for the values in each column and then gives TRUE or FALSE based on what it finds. The Multiplication Operator (*) changes these values into 0s and 1s, and then it does the multiplication, which changes all values except the one you want into 0.👉 The next function returns the position after searching the converted range for 1.👉 This function gives back the value. The INDEX function here shows the #N/A error if there are no matches. Moreover, the IFERROR function handles these types of errors and show the message “No Match.”
3. Utilizing INDEX-MATCH Functions to Match in Different Worksheets
Suppose, you need to use another worksheet to figure out how much each sale is made. Using the INDEX–MATCH formula is a simple way to do it. The INDEX-MATCH formula combines the INDEX with MATCH in Excel. Within a given table or range, the INDEX function generally returns either a value or a reference to a value. The MATCH function looks for a given item in a range of cells and then returns the relative position of that item within the range.
⬇️⬇️ STEPS ⬇️⬇️
- In the next example, “Region,” “Month,” and “Item” are given at random. On a different worksheet, we have to first find the “Item” for a certain “Month” and “Region.”
- In a new worksheet, make a new table with the columns “Region,” Month,” and “Item.” We will look for the result in this new worksheet.
- In cell D6 of the new worksheet, type this formula:
- Let’s copy the formula down. That’s how we find the results.
🔨 Formula Breakdown
👉 MATCH function looks for the match from the previously selected sheet.👉 The next function searches the converted range for 1 as the value. The function returns the position.👉 This function gives back values in the 9th row of price column. INDEX function here will return the #N/A error if there are no matches. We used IFERROR here to handle these kinds of errors and show the message “No Match.”
📕 Read More: 3 Easy Ways to Use INDEX-MATCH Across Multiple Sheets
4. Applying INDEX-MATCH Functions for Vertical and Horizontal Matches
You can also use a combination of the INDEX with MATCH to do horizontal and vertical lookups with more than one criterion. We will use the INDEX-MATCH combination. Within a given table or range, the INDEX function generally returns either a value or a reference to a value. The MATCH function looks for a given item in a range of cells and then returns the relative position of that item within the range. To do this, follow the steps below:
4.1 Search Vertically
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell G7 and type:
- Then press Enter to see the result.
4.2 Search Horizontally
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell G11 and type:
- Then press Enter to see the result.
🔨 Formula Breakdown
👉 MATCH function looks for the match from the previously selected sheet.👉 The next function searches the converted range for 1 and gives us the position.👉 This function gives back values in the 9th row of price column, which in turn, is the output that was asked for. The INDEX function here will return the #N/A error if there are no matches. We used IFERROR here to handle these kinds of errors and show the message “No Match.”
📕 Read More: Use INDEX Function to Match and Return Multiple Values Vertically
5. Matching Criteria with COUNTIFS Function
We can match multiple different criteria in Excel by using the COUNTIFS function. We use the statistical function COUNTIF to count how many cells meet a certain condition. Here, we are going to to use the AND logic to match varying criteria in varying columns and the OR logic for matching those criteria within the same column.
5.1 Combining with AND Logic
We use the statistical function COUNTIF to count how many cells meet a certain condition along with AND logic.
⬇️⬇️ STEPS ⬇️⬇️
- First, put the item’s region, month, and item in cells in the range G6:G8.
- Then, put this formula into cell G9.
- Then we press Enter. So, it’s the desired result.
🔨 Formula Breakdown
👉 The formula looks for the specific values in each column and takes them into the count when all of the conditions are met.👉 We can find one column only where all of the requirements are met. That is the result.
5.2 Combining with OR Logic
We use the statistical function COUNTIF to count how many cells meet a certain condition along with OR logic. The SUM function adds the two numbers together and gives back the result you want.
⬇️⬇️ STEPS ⬇️⬇️
- First, type the formula within cell G5 to find out how many cells meet the criteria:
- Then we press Enter. So, it’s the desired result.
🔨 Formula Breakdown
👉 The formula looks for the specific values in each column and takes them into the count when all of the conditions are met. COUNTIFS returns 4,2 because there are four “Apple”s and two “Banana”s.👉 The SUM portion adds two of the numbers together and gives back the result you want.
📕 Read More: 10 Cases to SUM with INDEX-MATCH for Multiple Criteria in Excel
How to Use Filter Function in Excel
FILTER function sorts a group of cells according to certain rules. For this method, you need not write formulas along with many different functions. For the operation to be done, you only need FILTER function.
⬇️⬇️ STEPS ⬇️⬇️
- First, put the region, month, and item’s name in the respective cells.
- Next, type the below formula in cell H9 to know price of the item that meets those criteria:
- Then we press Enter. We will get the result in cell H9.
🔨 Formula Breakdown
👉 The special multiplication operation will look for the values in each column and finds TRUE or FALSE based on what it finds. Multiplication Operator (*) changes these values into either 0s or 1s. After that, it does the multiplication, which changes all values except the one you want into 0.👉 Next, the function searches the converted range for 1 and gives us the position as output.👉 FILTER function looks for index numbers in the Sales column and gives back the value of the cell where the related index number is 1. Here the value is 55.
📄 Important Notes
🖊️ In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.
🖊️ A practice workbook is given so that you can practice yourself.
🖊️ All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.
🖊️ At the end of the Excel file, there is a sheet where they can practice.
📝 Takeaways from This Article
📌 The reader will be able to match multiple different criteria for different arrays in Excel easily with various methods.
📌 Readers can use both Array and Non-array formulas to match different arrays in Excel.
📌 Readers can also use the INDEX MATCH formula for looking in rows and columns.
📌 Moreover, users can do this job for multiple sheets.
📌 They can easily apply the COUNTIFS function for this operation.
📌 Viewers can utilize FILTER.
📌 One will be able to match these multiple criteria from many different arrays in Excel easily with the quickest method.
Conclusion
That concludes today’s session. These are the methods for matching multiple criteria from different arrays in Excel easily. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.