Lookup or Index to return multiple matches (or subsequent matches)

[Shawn Miller]

Hello,

I was curious how I could find subsequent matches when referring to a set of data that has multiple hits across the rows? I tried searching for how to find multiple returns or how to find subsequent returns but I could not find a post that shows how to do what I think should be rather simple.

Attached is am example file where I started a simple INDEX/MATCH and I am showing my desired output. Any ideas on how to do this efficiently? Probably with an array formula?

Thanks,
Shawn

 

[SirJB7]

Hi, Shawn Miller!
Quick & dirty:
=SI(C5="X";C$4&", ";"")&SI(D5="X";D$4&", ";"")&SI(E5="X";E$4&", ";"")&SI(F5="X";F$4&", ";"")&SI(G5="X";G$4&", ";"")&SI(H5="X";H$4&", ";"")&SI(I5="X";I$4&", ";"")&SI(J5="X";J$4&", ";"")&SI(K5="X";K$4&", ";"")&SI(L5="X";L$4&", ";"") -----> in English: =IF(C5="X",C$4&", ","")&IF(D5="X",D$4&", ","")&IF(E5="X",E$4&", ","")&IF(F5="X",F$4&", ","")&IF(G5="X",G$4&", ","")&IF(H5="X",H$4&", ","")&IF(I5="X",I$4&", ","")&IF(J5="X",J$4&", ","")&IF(K5="X",K$4&", ","")&IF(L5="X",L$4&", ","")
Copy down as required.
Regards!

 

[rahulshewale1]

Hii,,

Another opinion.
If you have excel 2O16..try below formula ..

=TEXTJOIN(",",1,IF($C$5:$L$5,$C$4:$L$4,""))

Note ;use Ctrl +shift +enter..

Thanks
Rahul Shewale..

 

[Chihiro]

If you have Office 365 subscription...
=TEXTJOIN(",",TRUE,IF($C5:$L5="X",$C$4:$L$4,""))

Confirmed as Array (CTRL + SHIFT + ENTER)

Other than that, you'd either need to restructure your data a bit...

Or use vba (UDF) I'd think.

 

[Shawn Miller]

Hi, Shawn Miller!
Quick & dirty:
=SI(C5="X";C$4&", ";"")&SI(D5="X";D$4&", ";"")&SI(E5="X";E$4&", ";"")&SI(F5="X";F$4&", ";"")&SI(G5="X";G$4&", ";"")&SI(H5="X";H$4&", ";"")&SI(I5="X";I$4&", ";"")&SI(J5="X";J$4&", ";"")&SI(K5="X";K$4&", ";"")&SI(L5="X";L$4&", ";"") -----> in English: =IF(C5="X",C$4&", ","")&IF(D5="X",D$4&", ","")&IF(E5="X",E$4&", ","")&IF(F5="X",F$4&", ","")&IF(G5="X",G$4&", ","")&IF(H5="X",H$4&", ","")&IF(I5="X",I$4&", ","")&IF(J5="X",J$4&", ","")&IF(K5="X",K$4&", ","")&IF(L5="X",L$4&", ","")
Copy down as required.
Regards!

Ah I was thinking incorrectly that I would hit the nested IF limit. I just took your formula and used LEFT and LEN to trim off the extra space and comma. Very long but it works, thank you!

 

[Shawn Miller]

Hii,,

Another opinion.
If you have excel 2O16..try below formula ..

=TEXTJOIN(",",1,IF($C$5:$L$5,$C$4:$L$4,""))

Note ;use Ctrl +shift +enter..

Thanks
Rahul Shewale..

Much cleaner but using Excel 2010

 

[SirJB7]

Hi, Shawn Miller!
Glad you solved it. I neither like nor knew if it was just a model or the actual file, but as I said, it was a Q&D approach. If upgrading to newer versions remember to switch to TEXTJOIN.
Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

 

[rahulshewale1]

hii,


try this one very long formula.
pfa sheet


=IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),1)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),2)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),3)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),4)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),5)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),6)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),7)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),8)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),9)),"")&","&IFERROR(INDEX($C$4:$L$4,AGGREGATE(15,6,(COLUMN($C4:$L4)-COLUMN($C4)+1)/($C5:$L5="x"),10)),"")



Thank
Rahul shewale