INDEX-MATCH Formula to Generate Multiple Results in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview picture to Use INDEX-MATCH Formula in Excel to Generate Multiple Results


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.

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


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)))

Typing Formula in cell C14  to Use INDEX-MATCH Formula in Excel to Generate Multiple Results

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

Applying the Fill Handle Tool

  • Hence, you can see the complete Marks.

Complete Marks Column

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.

GIF of the Result

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.

Dataset to Find the Multiple Results of Upcoming Event’s Name & Date with INDEX MATCH Formula 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)

Applying Formula to Find the Multiple Results of Upcoming Event’s Name & Date with INDEX MATCH Formula in Excel

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)

Employing Formula for Birthday Date

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

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.

Final Birthday Date

Read More: Excel INDEX-MATCH Formula to Return Multiple Values Horizontally


Similar Readings


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.

Dataset to Generate Multiple Results into Separate Columns using INDEX MATCH Formula in Excel

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))),"")

Employing formula in cell C14

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.

Use of Fill Handle Tool towards Right

  • In addition, we will drag down the formula with the Fill Handle tool.

Use of Fill Handle Tool toward down

  • Therefore, you can see the result in multiple columns.

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))),"")

Employing Formula Extract Multiple Results into Separate Rows utilizing INDEX MATCH Functions in Excel

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.

Using Fill Handle Tool Towards Right

  • In addition, we will drag down the formula with the Fill Handle tool.

Inserting Fill Handle tool Downward

  • Therefore, you can see the result in multiple rows.

The Output to Extract Multiple Results into Separate Rows utilizing INDEX MATCH Functions in Excel


Practice Section

You can download the above Excel file and practice the explained methods.

Practice Section


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

2 Comments
  1. 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!

    • Reply Avatar photo
      Naimul Hasan Arif Aug 30, 2022 at 12:05 PM

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo