How to extract multiple matches into separate rows in Excel

How to extract multiple matches into separate rows in Excel

Writing content will involve in all aspects the use of multiple letters or words with the same meaning. These matches are no offense to be there in the content that you are writing because it is common and it will keep on happening.

The same case of having multiple matches happens also when we are using the excel sheets. You will find that multiple columns, rows, or the cells have the same values or even the same names. It is common because even humans share names.

Now that we know excel sheets may have multiple matches, we can try and see if you can extract those matches into separate rows aside from the original data set.

There are several steps to follow to come up with the correct extraction of multiple matches; they may include the ones below.

Step 1

Key in a data set like the one below in your new excel sheet on your laptop.

Step 2

With our data set in the above step, we are now going to do the extraction of multiple matches. In the above data set, we are going to extract the values that match those of fox and those of bear to the column with fox and that with the bear.

To do the extraction successfully, we are going to use some functions in excel. These functions are; SMALL, INDEX, and IFERROR. The SMALL function is used to get the row number that corresponds to the row you have typed for example E5.

The INDEX function, on the other hand, will return the value of the nth row found by the SMALL function. IFERROR function is used to push and handle errors that may arise.

The general formula of the extraction will be; {=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),””)}. The formula will be written on the formula bar.

This formula has dynamic values like the names of the cells which will be different for each cell you need to get the match too. Repeat the formula on all the cells of interest to get the correct match values like on the scenario below in the screenshot format.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.