Return Row Header for multiple occurences with a formula

[rlkcpo]

Hello. I did a search and really couldn't find anything that returned row header information for this kind of problem. Hopefully someone here can lend me a hand.

So here is what I am trying to do [table="width: 500, class: grid"]
[tr]
[td]Name[/td]
[td]English[/td]
[td]German[/td]
[td]French[/td]
[/tr]
[tr]
[td]John[/td]
[td]Yes[/td]
[td]Yes[/td]
[td]Yes[/td]
[/tr]
[tr]
[td]Kim[/td]
[td]Yes[/td]
[td]Yes[/td]
[td][/td]
[/tr]
[tr]
[td]George[/td]
[td]Yes[/td]
[td][/td]
[td]Yes[/td]
[/tr]
[/table]

Basically I need to be able to lookup who speaks a particular language. So if in Cell F2 I enter English, Cells G2:G4 lists "John, Kim, George" respectfully, or if German is entered into F2, Cells G2:G3 lists "John, Kim" respectfully.

I am able to do this with columns but for some reason I cant see to accomplish this and return the row. Thanks in advance for any help!

 

[rlkcpo]

Here is the formula that I am using for the columns- maybe this will jumpstart the conversation

=IFERROR(IF(F2<>"",INDEX($B$1:$D$1,SMALL(IF(INDEX($B$2:$D$4,MATCH(F2,$A$2:$D$4,0),0)="Yes",COLUMN($B$1:$D1)-COLUMN($B2)+1),1)),""),"")

 

[rlkcpo]

So if you google "return row header" (with quotes) this thread is the #2 return. Seems to me that this question should have been asked and answered long ago. Did I happen to stumble upon an array problem with no answer?

 

[rlkcpo]

Still nothing eh?

 

[rlkcpo]

So over 50 views and half a day later and still no reply. Can anyone point me in the direction where I might be able to find someone that can answer this? Maybe a specialty board or some such?

 

[T. Valko]

Try this...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][td="bgcolor: #c0c0c0"]

B​

[/td][td="bgcolor: #c0c0c0"]

C​

[/td][td="bgcolor: #c0c0c0"]

D​

[/td][td="bgcolor: #c0c0c0"]

E​

[/td][td="bgcolor: #c0c0c0"]

F​

[/td][td="bgcolor: #c0c0c0"]

G​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td]

Name​

[/td][td]

English​

[/td][td]

German​

[/td][td]

French​

[/td][td]

------​

[/td][td]

Language​

[/td][td]

Name​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td]

John​

[/td][td]

Yes​

[/td][td]

Yes​

[/td][td]

Yes​

[/td][td][/td][td]

French​

[/td][td]

John​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

3​

[/td][td]

Kim​

[/td][td]

Yes​

[/td][td]

Yes​

[/td][td][/td][td][/td][td][/td][td]

George​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

4​

[/td][td]

George​

[/td][td]

Yes​

[/td][td][/td][td]

Yes​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

This array formula** entered in G2:

=IFERROR(INDEX(A:A,SMALL(IF(INDEX(B$2:D$4,0,MATCH(F$2,B$1:D$1,0))="Yes",ROW(B$2:D$4)),ROWS(G$2:G2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[rlkcpo]

Works fantastic! Thank you so much!

 

[T. Valko]

You're welcome. Thanks for the feedback!