How To Extract Multiple Matches Into Separate Rows In Excel || Excel Tips & Tricks || dptutorials

In this tutorial let us see how to extract multiple matches into separate rows in Excel
Here we will be using an array formula with a combination of Index and small functions

Let us consider an example like this now.

Best Laptops to use for better speed:

I have categories in column B and names in column C

And let us try to extract the names into these rows from E5 to G8, which are matching with the categories of E4 to G4.

So I enter the formula in E5 as
=INDEX($C$4:$C$13,
we will be using the SMALL function here to get a row number that corresponds to an "nth match".

So continue the formula by typing as
SMALL(IF($B$4:$B$13=E$4,ROW($C$4:$C$13)-MIN(ROW($C$4:$C$13))+1),1))

Press Ctrl+Shift+Enter, to see the result as “Apple” which is our first fruit name in the data range.

To make the k value more dynamic, we replace this 1 by typing ROWS($E$5:E5)
So this is our final formula
=INDEX($C$4:$C$13,SMALL(IF($B$4:$B$13=E$4,ROW($C$4:$C$13)-MIN(ROW($C$4:$C$13))+1),ROWS($E$5:E5)))

Press Ctrl+Shift+Enter again to see the result and now drag this formula to the end and to the right side to see results in all cells, you will see errors like this if there are no matches.

To avoid errors like this we cover this formula by iferror function like this

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

Got it friends.

So, friends this is how you can extract multiple matches into separate rows in Excel

I hope you have enjoyed this tutorial, If yes, please do give me a like, share and comment.

For further more interesting videos, please do subscribe dptutorials.

Our Recommendations
***************************************************************

If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.

***********************************************

⚡️Tools for YouTube vlogging:
***********************************************
***********************************************

⚡️You Can Connect with Me at:
***********************************************

#dptutorials #Exceltraining #Extractmultiplematches #ExcelTips #ExcelFreeTraining #ExcelFreeLearning

⚡️Tags: -
excel formulas in English, excel in English, excel tutorial in English,ms excel in english,ms excel tutorial in English, learn excel in English,vlookup in excel in English, learn ms excel in English, excel training, excel tutorial, Microsoft Excel 2007, learn excel,tutorial excel, ms excel tutorial, excel tutorials,ms excel 2007,Microsoft Excel training,learn excel online,learning excel,free excel training,online excel training,advanced excel tutorial,excel tutorial, excel formulas and functions, excel formulas, excel tutorial in Hindi, excel formulas and functions in Hindi, excel tricks, excel in Hindi, excel shortcut keys, excel vlookup, excel formulas in Hindi, excel for beginners

⚡️Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

[emilythechef]

THANK YOU SO I have never heard of the SMALL function, ad when I have some time I'll deconstruct this awesome array formula, but in the mean time, it worked great and gave me exactly the results I was looking for. I need to learn more about Arrays!

[tonipeter]

Thank you! I Index and Match all the time, but I’ve never used the Small formula. Just what I needed.

[weqtechnologies-softwaredi]

Really Helpful and good level of explanation.

[shastryanil]

Sir nice video, it worked in one of excel work book. However when I trying to use the same formula in another excel workbook which has blank cells in my range the SMALL function is throwing an error. Can you please help me how to get rid of it.

[nexsel]

Yar yahi to me dhund rha tha bs, thanks a lot dude🙏

[macky_Clips]

Hi Dp tutorials. i learn so much from your tutorials thank you so much. Can i request a tutorial on How to Paste a data on a filtered cells on excel, this would be great for the viewers Thank you so much.

[vigneshbaskar]

Kindly help for multiple matches like multiple if condition

[syedammar]

Thanks DP. Is there a way to add unique function to this formula to avoid duplicates?

[MrRahulBerry]

Instead of displaying the result Vertically, how can we keep the result horizontally. Like 3 "Fruit" listed in Rows (listed all 3 times) shows all match records Apple, Orange, Mango, 3 records in this example for Fruit

[margoharriman]

I am getting a circular reference error, what have I done wrong?

[technicalskgiddu]

Aap hame ye bataye ki harek 3 row ke bad heading chahiye, kaishe kare

[firstlove]

Hi, friend, your lesson is great, thanks but now i has a question, could you teach me how to create 250 Search boxs for for different group so fast in excel ? not use drop down list, Example : go to Conditional formatting and go to new rule and click Use a Formula to determine and type =$C$4=A1. HI. friend i don't to like create one by one is take too longs, please help me

[prateekbansal]

Hi, do you have an email id or some way I can get in touch with you? I have a doubt regarding this formula and would really appreciate if you look into it.

[cyrilz]

How can we apply the same formula but with two conditions, not only the condition of fruits. please can anyone give me the formula, thanks

[muharremozturk]

Hi, it is really nice tutorial but ı neede help little bit different way. I have two columns have data and I copied one column data to another place and sort them different criteria and I use index formul to get second column data. MY PROBLEM IS THAT There is not problem in unique data. I should make it return 1- x time in non unique value, as repeat to every different kind of data or section. İf you wanna help me, I could send example to you. for now thanks you. ı hope I could tell my problem.