How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results

While working with a large number of data leads to producing multiple results. In Excel, there is a very convenient way to extract multiple results. In this article, we will show you how to use the INDEX-MATCH formula in Excel to generate multiple results.


Download Practice Template

You can download the free practice Excel template from here and practice on your own.


4 Useful Ways in Implementing the INDEX & MATCH Functions Together in Excel to Extract Multiple Results

This section will discuss in detail the utilization of the INDEX function and the MATCH function together in Excel to get multiple results.

1. Find Multiple Results in Array using INDEX MATCH Formula in Excel

Consider the following dataset, where we wanted to find the different marks of students of different exams.

We can get that by running an INDEX-MATCH formula in our worksheet.

Steps on finding the multiple results in a set of an array using INDEX-MATCH functions are given below.

Steps:

  • 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 G4).
  • In another cell that you want as your result cell (e.g. Cell G7), write the following formula,
=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)))

Here,

$C$5:$C$11 = the array to search the lookup value

$B$5:$B$11 = the array where the lookup value is

  • Press Enter.

index match multiple results for array

You will notice that the result for the data (e.g. Villiers) that you put in the cell selected (G4), will be appeared in the result cell (e.g. 65 in G7).

  • Now drag the row down by Fill Handle to get the rest of your results of that same lookup value.

index match multiple results for array

As this process is not constant for any specific value, so you can pick any lookup data in the cell selected (e.g. G4) and the result for that particular data will be auto-updated in the result cell (e.g. G7).

To understand more see the gif below.

index match multiple results

Formula Breakdown:

Let’s break down the formula to understand how we got the result.

  • 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}

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, $G$4, 0) becomes,

-> ISNUMBER({#N/A; #N/A; #N/A; 1; #N/A; 1; #N/A})

-> Output: {FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}.

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, $G$4, 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, $G$4, 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

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

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


2. Find the 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.

Below is the sample data where we wanted to know the upcoming birthday among our friends.

So, what we did was, implement an INDEX MATCH formula to find out who’s birthday it is next and when is that.

So, the formula to know the person’s name or the upcoming event’s name is,

=INDEX($B$5:$B$9,MATCH(F5,$C$5:$C$9,1)+1)

index match multiple results for event

And the formula to know the date of the upcoming event is,

=INDEX($C$5:$C$9,MATCH(F5,$C$5:$C$9,1)+1)

index match multiple results for event date

Formula Breakdown:

Let’s break down the formula to understand how we found out the name Alex and the date of his birthday.

  • MATCH(F5,$C$5:$C$9,1)

-> Output: 4

Explanation: The MATCH Function finds the position of the lookup value (Cell F5 =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(F5,$C$5:$C$9,1)+1) becomes

-> INDEX($B$5:$B$9, 4) +1)

-> Output: Alex/(The event’s name)

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(F5,$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 range.

And,

  • INDEX($C$5:$C$9,MATCH(F5,$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(F5,$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 range.

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.


3. Generate Multiple Results into Separate Columns using INDEX MATCH Formula in Excel

Until now we have been getting results row-wise. But what if you want to get the results in separate columns.

Consider the following dataset, which consists of three types of professions representing multiple names of people.

We wanted to make a group of people based on the profession and we wanted to place the names column-wise according to their profession.

To extract multiple match results into separate cells in separate columns, you can do a little trick with the INDEX function.

Let’s learn how to extract multiple match results in multiple columns in Excel with a combination of INDEX functions and others.

Steps:

  • 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 E5).
  • In another cell that you want as your result cell (e.g. Cell F5), write the following formula,
=IFERROR(INDEX($C$5:$C$11,SMALL(IF($B$5:$B$11=$E5,ROW(C5:C11)-MIN(ROW(C5:C11))+1),COLUMNS($E$5:E5))),"")

Here,

$C$5:$C$11 = the array to search the lookup value

$B$5:$B$11 = the array where the lookup value is

  • Press Enter.

index match multiple results in multiple columns

You will notice that the result for the data (e.g. Cricketers) that you put in the cell selected (E5), will be appeared in the result cell (e.g. Flintoff in Cell F5).

  • Now drag the row around by Fill Handle to get the rest of your results of that same lookup value in separate multiple columns.

index match multiple results in multiple columns result

  • Again, drag the row around by Fill Handle to get the rest of your results of different lookup values (e.g. Footballer, Wrestlers) in separate multiple columns.

index match multiple results in multiple columns and rows

Formula Breakdown:

Let’s break down the formula to understand how we got the result.

  • SMALL(IF($B$5:$B$11=$E5,ROW($C$5:$C$11)-MIN(ROW($C$5:$C$11))+1),COLUMNS($E$5:E5))

We already know from the previous discussion how SMALL, IF and ROW functions work together, and we are using that trick here too for generating a row number corresponding to an N-th match. Once we have the row number, we simply pass it into the INDEX function which returns the value at that row.

-> Output: (as the picture below)

  • INDEX($C$5:$C$11,SMALL(IF($B$5:$B$11=$E5,ROW($C$5:$C$11)-MIN(ROW($C$5:$C$11))+1),COLUMNS($E$5:E5))) -> passing the row numbers to the INDEX function to extract the value for corresponding row numbers.

-> Output: (as the picture below)

  • IFERROR(INDEX($C$5:$C$11,SMALL(IF($B$5:$B$11=$E5,ROW(C5:C11)-MIN(ROW(C5:C11))+1),COLUMNS($E$5:E5))),””) -> notice that when columns return a value that does not exist, it throws a #NUM error. To prevent the error, we wrap the whole formula with the IFERROR function to catch errors and set an empty string (“”) as a return.

-> Output: (as the picture below)


4. Extract Multiple Results into Separate Rows utilizing INDEX MATCH Functions in Excel

If you are wondering about extracting multiple results into multiple rows as the same way that we did in our previous section, then the formula is,

=IFERROR(INDEX($C$5:$C$11,SMALL(IF($B$5:$B$11=E$4,ROW(C5:C11)-MIN(ROW(C5:C11))+1),ROWS($E$5:E5))),"")

index match multiple results in multiple rows

And every other details such as Steps and Formula Breakdown are the same as in section 3.


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.
  • When 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. Don’t type those brackets {} yourself, Excel automatically does this for you.

Conclusion

This article explained in detail how to use INDEX MATCH functions to extract 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 May Also Like To Explore

Sanjida Ahmed

Hello, this is Sanjida, an Engineer who loves Sports a lot. Here I try to solve Excel problems with you. Hope I could be a great help to you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo