Microsoft Excel

Extract Multiple Matches into Separate Rows in Microsoft Excel

Learn to Extract Multiple Matches into Separate Rows in Microsoft Excel

The main purpose of this formula is to extract multiple matches to separate cells in separate rows. Here we will learn how to extract multiple matches into separate rows in the given data range in the workbook in Microsoft Excel.

That implies, with the help of a formula based on the INDEX and SMALL functions, you can able to extract multiple matches to separate cells in separate rows.

So, with the help of this formula, you can able to extract multiple matches into separate rows in the given data range in the workbook in Microsoft Excel.

 

General Formula to Extract Multiple Matches into Separate Rows 

=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),””)

 

The Explanation to Extract Multiple Matches into Separate Rows

Extract Multiple Matches into Separate Rows in Microsoft Excel

 

So we know that with the help of the given formula above, you can able to extract multiple matches to separate cells in separate rows. Here we will learn how to extract multiple matches into separate rows in the given data range in the workbook in Microsoft Excel.

As we know that we are using the SMALL function to generate a row number that corresponds to the “nth match”. When we have the row number, we simply pass it into the INDEX function, which returns the value at that row.

So now you have learned how to extract multiple matches into separate rows in the given data range in the workbook in Microsoft Excel.