Sanjida Ahmed
Working with a large number of data leads to producing multiple results. In this article, we will show you how to use the INDEX-MATCH formula in Excel to generate multiple results.
In the following picture, you can see the overview of using the INDEX-MATCH formula in Excel to generate multiple results. After that, let’s get into the article to find out how you can generate multiple results as well.
Download Practice Workbook
You can download the free practice Excel template from here and practice on your own.
4 Suitable Examples to Use INDEX-MATCH Formula to Generate Multiple Results in Excel
The following dataset has the Name and Marks columns. Furthermore, using this dataset, we will discuss in detail the utilization of the INDEX function and the MATCH function together in Excel to get multiple results. Here, we used Excel 365. You can use any available Excel version.
1. Finding Multiple Results in Array by Using INDEX MATCH Formula in Excel
In this method, we will find multiple results in a set of arrays using an INDEX-MATCH formula in our worksheet. We will use the ISNUMBER, ROW, ROWS, and SMALL functions to execute the formula.
Steps:
- First of all, we will choose a name from the dataset (B5:B11) and put the name in another cell to use the cell reference number later (e.g. name Villiers in Cell B14:B15).
- After that, in another cell that you want as your result cell (e.g. Cell C14), write the following formula.
=INDEX($C$5:$C$11, SMALL(IF(ISNUMBER(MATCH($B$5:$B$11, $B$14, 0)), MATCH(ROW($B$5:$B$11), ROW($B$5:$B$11)), ""), ROWS($A$1:A1)))
Formula Breakdown
- MATCH($B$5:$B$11, $G$4, 0) → becomes,
- MATCH({“Flintoff”; “Ronaldo”; “Plessis”; “Villiers”; “Beckham”; “Villiers”; “Plessis”}, “Villiers”, 0)
- Output: {#N/A; #N/A; #N/A; 1; #N/A; 1; #N/A}
- MATCH({“Flintoff”; “Ronaldo”; “Plessis”; “Villiers”; “Beckham”; “Villiers”; “Plessis”}, “Villiers”, 0)
- Explanation: If the search value finds a match in the lookup array, then the MATCH function returns 1, otherwise it returns #N/A.
- ISNUMBER(MATCH($B$5:$B$11, $B$14, 0) → becomes,
- ISNUMBER({#N/A; #N/A; #N/A; 1; #N/A; 1; #N/A})
- Output: {FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}.
- ISNUMBER({#N/A; #N/A; #N/A; 1; #N/A; 1; #N/A})
- Explanation: As the IF Function is unable to handle error values, so the ISNUMBER Function is being utilized here to convert the array values to Boolean values.
- IF(ISNUMBER(MATCH($B$5:$B$11, $B$14, 0)), MATCH(ROW($B$5:$B$11), ROW($B$5:$B$11)), “”) becomes,
- IF({FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}, MATCH(ROW($B$5:$B$11), ROW($B$5:$B$11)), “”) → becomes
- IF({FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7}, “”)
-
- Output: {“”; “”; “”; 4; “”; 6}
-
- Explanation: Firstly, the IF Function converts the Boolean values into row numbers and blanks. Later, the MATCH and the ROW Functions calculate an array with consecutive numbers, from 1 to n, where n is the last numeric identity of the total size of the cell range. As $B$5:$B$11 has 7 values, so the array becomes {1; 2; 3; 4; 5; 6; 7}.
- SMALL(IF(ISNUMBER(MATCH($B$5:$B$11, $B$14, 0)), MATCH(ROW($B$5:$B$11), ROW($B$5:$B$11)), “”), ROWS($A$1:A1))) → becomes
- SMALL({“”; “”; “”; 4; “”; 6}, ROWS($A$1:A1)) → becomes
- SMALL({“”; “”; “”; 4; “”; 6}, 1)
- Output: 4
- SMALL({“”; “”; “”; 4; “”; 6}, 1)
- Explanation: First, the SMALL Function determines which value to get based on the row number. Next, the Rows Function returns a number that changes every time the cell gets copied and pasted to the cells below. Initially, it returned 4 according to our dataset. In the next cell below, ROWS($A$1:A1) changes to ROWS($A$1:A2) and returns 6.
- INDEX($C$5:$C$11, SMALL(IF(ISNUMBER(MATCH($B$5:$B$11, $G$4, 0)), MATCH(ROW($B$5:$B$11), ROW($B$5:$B$11)), “”), ROWS($A$1:A1))) → becomes
- INDEX($C$5:$C$11, 4)
- Output: 65
- INDEX($C$5:$C$11, 4)
- Explanation: The INDEX function returns a value from a given array based on a row and column number. The 4th value in the array $C$5:$C$11 is 65, so the INDEX function returns 65 in cell C14.
- After that, press ENTER.
- Therefore, you can see the result in cell C14.
- Moreover, we will drag the row down by Fill Handle to get the rest of your results of that same lookup value.
- Hence, you can see the complete Marks.
As this process is not constant for any specific value, so you can pick any lookup data in the cell selected (e.g. B14:B15) and the result for that particular data will be auto-updated in the result cell (e.g. C14 and C15).
To understand more see the gif below.
Read More: IF with INDEX-MATCH in Excel (3 Suitable Approaches)
2. Find Multiple Results of Upcoming Event’s Name & Date with INDEX-MATCH Formula in Excel
Sometimes we forget about the date of important events. In that case, the INDEX MATCH functions work as the rescuers.
in the following picture, you can see a sample data where we wanted to know the upcoming birthday of our friends.
Steps:
- First of all, we will type the following formula in cell C12.
=INDEX($B$5:$B$9,MATCH(C11,$C$5:$C$9,1)+1)
Formula Breakdown
- MATCH(C11,$C$5:$C$9,1)
- Output: 4
- Explanation: The MATCH Function finds the position of the lookup value (Cell C11 =Thursday, November 11, 2021) in the array constant ($C$5:$C$9 = the list of the dates). In this example, we didn’t want an exact match, we wanted the MATCH function to return an approximate match, so we set the third argument to 1 (or TRUE).
- INDEX($B$5:$B$9,MATCH(C11,$C$5:$C$9,1)+1) becomes
- INDEX($B$5:$B$9, 4) +1)
- Output: Alex (The name of the person whose birthday is coming next)
- Explanation: The INDEX Function takes two arguments to return a specific value in a one-dimensional range. Here, the range $B$5:$B$9 is first the argument and the result that we had from the calculation in the previous section (MATCH(C11,$C$5:$C$9,1)), position 4, is the second argument. That means we are searching the value located in position 4 in the $B$5:$B$9
- After that, press ENTER.
- Therefore, you can result in cell C12.
- Furthermore, to know the Birthday Date, we will type the following formula in cell C13.
=INDEX($C$5:$C$9,MATCH(C11,$C$5:$C$9,1)+1)
Formula Breakdown
- INDEX($C$5:$C$9,MATCH(C11,$C$5:$C$9,1)+1) becomes
- INDEX($B$5:$B$9, 4) +1)
- Output: Tuesday, December 7, 2021
- Explanation: The INDEX Function takes two arguments to return a specific value in a one-dimensional range. Here, the range $C$5:$C$9 is first the argument and the result that we had from the calculation in the previous section (MATCH(C11,$C$5:$C$9,1)), position 4, is the second argument. That means we are searching the value located in position 4 in the $C$5:$C$9
- INDEX($B$5:$B$9, 4) +1)
To get the upcoming event date, we just added one to the cell position returned by the MATCH function, and it gave us the cell position of the next event date.
- After that, press ENTER.
- As a result, you can see the output in cell C13.
Read More: Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
Similar Readings
- INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)
- How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)
- INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)
- How to Match Multiple Criteria from Different Arrays in Excel
3. Generate Multiple Results into Separate Columns Using INDEX-MATCH Formula in Excel
Here, we will use the INDEX-MATCH formula in Excel to generate multiple results in multiple columns. We will use the combination of IFERROR, INDEX, ROW, IF, SMALL, COLUMNS, and MIN functions to do the task.
Consider the following dataset, which consists of three types of professions representing multiple names of people. We want to make a group of people based on their profession and we wanted to place the names column-wise according to their profession.
Steps:
- First of all, we will choose a profession from the data range (B5:B11) and put the data in another cell to use the cell reference number later (e.g., profession Cricketer in Cell B14).
- After that, in another cell that you want as your result cell (e.g. Cell C14), write the following formula,
=IFERROR(INDEX($C$5:$C$11,SMALL(IF($B$5:$B$11=$B14,ROW($C$5:$C$11)-MIN(ROW($C$5:$C$11))+1),COLUMNS($B$14:B14))),"")
Formula Breakdown
- IF($B$5:$B$11=$B14 → becomes
- IF{“Cricketer”, “Footballer”, “Wrestler”, “Cricketer”, “Footballer”, “Wrestler”, “Cricketer”}
- Output: IF{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
- ROW($C$5:$C$11) → becomes
- Output: {5;6;7;8;9;10;11}
- MIN(ROW($C$5:$C$11) → becomes
- MIN({5;6;7;8;9;10;11})
- Output: 5
- {5;6;7;8;9;10;11}-5 → becomes
- Output: {0;1;2;3;4;5;6}
- {0;1;2;3;4;5;6}+1→ becomes
- Output: {1;2;3;4;5;6;7}
- IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE},{1;2;3;4;5;6;7})→ As a result, it becomes
- {1;FALSE;FALSE;4;FALSE;FALSE;7}
- COLUMNS($B$14:B14) → becomes
- Output: 1
- SMALL({1;FALSE;FALSE;4;FALSE;FALSE;7},1) → Hence, it becomes
- Output:1
- INDEX($C$5:$C$11,1) → becomes
- Output: Flintoff
- IFERROR(Flintoff, “ “) → It therefore becomes
- Output: Flintoff
- At the moment, press ENTER.
- Hence, you can see the result in cell C14.
- Furthermore, we will drag the formula toward the right with the Fill Handle tool.
- In addition, we will drag down the formula with the Fill Handle tool.
- Therefore, you can see the result in multiple columns.
Read More: Excel INDEX MATCH to Return Multiple Values in One Cell
4. Extraction of Multiple Values into Separate Rows Utilizing INDEX-MATCH Functions in Excel
Here, we will use the INDEX-MATCH formula in Excel to generate multiple results in multiple rows. We will use the combination of IFERROR, INDEX, SMALL, IF, ROW, MIN, and ROWS function for that purpose.
Steps:
- In the first place, type the following formula in cell B14.
=IFERROR(INDEX($C$5:$C$11,SMALL(IF($B$5:$B$11=B$13,ROW(C5:C11)-MIN(ROW(C5:C11))+1),ROWS($B$14:B14))),"")
This formula is almost the same as the formula we used in Method-3. However, we used the ROWS function instead of the COLUMNS function, and this is the only change.
- Afterward, press ENTER.
- As a result, you can see the result in cell B14.
- Furthermore, we will drag the formula toward the right with the Fill Handle tool.
- In addition, we will drag down the formula with the Fill Handle tool.
- Therefore, you can see the result in multiple rows.
Practice Section
You can download the above Excel file and practice the explained methods.
Key Points You Must Keep in Mind
- As the range of the data table array to search for the value is fixed, don’t forget to put the dollar ($) sign in front of the cell reference number of the array table.
- While working with array values, don’t forget to press Ctrl + Shift + Enter on your keyboard while extracting results. Pressing only Enter will work only when you are using Microsoft 365.
- After pressing Ctrl + Shift + Enter, you will notice that the formula bar enclosed the formula in curly braces {}, declaring it as an array formula. Also, don’t type those brackets {} yourself, Excel automatically does this for you.
Conclusion
This article explained in detail how to use the INDEX-MATCH formula to generate multiple results in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic. You can visit our website Exceldemy to explore more.
You May Also Like To Explore
- Index Function to Match and Return Multiple Values Vertically in Excel
- Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)
- How to Use INDEX and Match for Partial Match (2 Ways)
- Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
- Index Match Multiple Criteria in Rows and Columns in Excel
- How to Select Specific Data in Excel (6 Methods)
Hi there!
It’s an excellent article, very useful, well explaind steps. I got here because have some issues with Index-Match functions, it is like described under pont 4.: Extract Multiple Results into Separate Rows utilizing INDEX MATCH Functions. Only difference is that I would like to extract the entire row where the match exists. More precisely: I have a lot of data in more columns, one column contains the company names. I would like to extract entire rows for a given company. I don’t know how to implement that difference into Your solution. Can You please give me some advice or guideline?
Thanks in advance!
Thanks ANDREW for your query.
You can check out the following formula that I have applied with the dataset mentioned in the image.
=INDEX(B5:E12,MATCH($B$15,$D$5:$D$12,0),0)
It gives the entire row with the first matched value. But it will not give all the matched rows as output. You can modify the formula using any aggregate function like SMALL to get all the rows at one go.