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

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.

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.

**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 Function**s 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)`

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

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

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.

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

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

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.