Filter Multiple Columns and List Column Headers in Array

[zjamespryor]

Hello All,

I am using Google Sheets for this project. I have a dataset with many columns that have similar values. In the example below, they are "Gold," "Silver," and "Bronze." I need to filter my data by the person's "Name," and return the column headers in a list. I have almost 100 columns of "sports" in my data, which is why I need to filter the results down rather than just being able to list out each "sport" individually. Thank you for any help with this you can offer.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Select Person ->	James			Dataset
3					School	Name	Track	Wrestling	Golf	Baseball
4	Sports (Desired Results)	Medals			Virginia	James	Gold		Silver	Bronze
5	Track	Gold			California	Dan		Bronze	Gold	Silver
6	Golf	Silver			Nebraska	Bill	Silver	Gold	Bronze
7	Baseball	Bronze			Florida	Frank	Bronze	Silver		Gold
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=$F$4:$F$7

 

[Domenic]

Try...

A5:

=TRANSPOSE(FILTER($G$3:$J$3,LEN(INDEX($G$4:$J$7,MATCH($B$2,$F$4:$F$7,0),0))>0))

B5:

=TRANSPOSE(LET(Metals,INDEX($G$4:$J$7,MATCH($B$2,$F$4:$F$7,0),0),FILTER(Metals,LEN(Metals)>0)))

Hope this helps!

 

[zjamespryor]

Works great! The answer is always simpler than I think. I'm unfamiliar with using LEN so I'm going to have to practice with it some. Google Sheets doesn't allow LET, but I was able to fill-in FILTER with the INDEX your provided and it works great.

 

[Domenic]

That's great, I'm glad you were able to work it so that it worked for you.

Cheers!