How to Use INDEX-MATCH Function for 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

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.

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


1. Using INDEX-MATCH Function for Multiple Results with Arrays 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 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)))

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


2. Finding Multiple Results of Upcoming Event’s Name & Date with INDEX-MATCH 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.

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

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)

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


3. Using Excel INDEX-MATCH Function to Get Multiple Results in Separate Columns 

Here, we will use the INDEX-MATCH 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 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


4. Extracting Multiple Values into Separate Rows with INDEX-MATCH Function 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


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


<< Go Back to INDEX MATCH | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

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

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

      Using INDEX-MATCH functions to get multiple results

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

      Combining IFERROR function with INDEX-MATCH formula

      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 INDEX-MATCH method, apply the following formula in the required cell and press the Enter key.

      =FILTER(C5:C11,B5:B11=B14)

      Using FILTER function instead of INDEX-MATCH formula

      I hope this solution will be helpful for you. Let us know your feedback.

      Regards,
      Seemanto Saha
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo