Need AGGREGATE to list when category is mixed up with others under one cell

opti_mind

New Member
Joined
Sep 22, 2016
Messages
2
Hello, I have a Data-Validation Drop Down on Cell A1 that contains Board Game Categories and whenever I click them, I need excel to pull all the Board Games associated with that category and list them down in COLUMN A. So far, my code is only pulling Board Games that has that specific category name singled out in one cell [see purple text in image]. I need excel to also pull Board Games when the category name is mixed up with other different category names under one cell [see red text in image].

This is the code on Cell A2, that I would appreciate any kind of help I can get with to get it to work. Thank You.

=INDEX($C$2:$C$156,AGGREGATE(15,6,(ROW($D$2:$D$156)-ROW($D$2)+1)/($D$2:$D$156=$A$1),ROWS(A$2:A2)))

 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,408
Office Version
365
Platform
Windows
Hi, welcome to your first post!

You could try like this.

=INDEX($C$2:$C$156,AGGREGATE(15,6,(ROW($D$2:$D$156)-ROW($D$2)+1)/ISNUMBER(SEARCH($A$1,$D$2:$D$156)),ROWS(A$2:A2)))
 
Last edited:

opti_mind

New Member
Joined
Sep 22, 2016
Messages
2
FormR, thank you so much. That solved the problem with just a simple addition of code. AWESOME :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,099,472
Messages
5,468,823
Members
406,611
Latest member
hanman453

This Week's Hot Topics

Top