Working with a large number of data leads to producing multiple results. In this article, we will show you how to use the INDEXMATCH formula in Excel to generate multiple results.
In the following picture, you can see the overview of using the INDEXMATCH 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.
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 Microsoft 365. You can use any available Excel version.
1. Using INDEXMATCH Function for Multiple Results with Arrays in Excel
In this method, we will find multiple results in a set of arrays using an INDEXMATCH formula in our worksheet. We will also use the ISNUMBER, ROW, ROWS, and SMALL functions to execute the formula. Let’s follow the instructions below to use INDEX MATCH with multiple criteria for multiple results!
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 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 autoupdated in the result cell (e.g. C14 and C15).
To understand more see the gif below.
2. Finding Multiple Results of Upcoming Event’s Name & Date with INDEXMATCH Function 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.
Go through the process below to use the INDEX MATCH with multiple criteria for date range in Excel!
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 onedimensional 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 onedimensional 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.
3. Using Excel INDEXMATCH Function to Get Multiple Results in Separate Columns
Here, we will use the INDEXMATCH formula with multiple criteria 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 columnwise 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.
4. Extracting Multiple Values into Separate Rows with INDEXMATCH Function in Excel
Here, we will use the INDEXMATCH 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 Method3. 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.
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.
Download Practice Workbook
You can download the free practice Excel template from here and practice on your own.
Conclusion
This article explained in detail how to use the INDEXMATCH 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.
<< Go Back to INDEX MATCH  Formula List  Learn Excel
Hi there!
It’s an excellent article, very useful, well explaind steps. I got here because have some issues with IndexMatch 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.
EMEĞİNİZE SAĞLIK HOCAM =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))) OFİS 365 KULLANIYORUM BU FORMÜLÜ OFİS 365 E GÖRE NASILYAPILIR
Dear AHMET KARAASLAN,
Thanks for your comment.
Although the mentioned formula is an array formula, you can use it in Excel for Microsoft 365 without any modifications.
Enter the formula in your required cell and press the Enter key. After that drag down the Fill Handle icon.
Note: This formula can return #NUM! error if any match isn’t found when you use the Fill Handle feature. To avoid this you can combine the IFERROR function with your formula. The modifier formula is:
=IFERROR(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:A9))),””)
However, if you want to avoid using the Fill Handle feature and want all match results with a single formula, then you can use the FILTER function. This function is only available in Excel for Microsoft 365 and can filter a range based on any given criteria.
To get the same result as the INDEXMATCH method, apply the following formula in the required cell and press the Enter key.
=FILTER(C5:C11,B5:B11=B14)
I hope this solution will be helpful for you. Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy