Lookup....

[sssb2000]

Hello

i have a table (A2:E10). column A contains names. Columns B:E contain either "yes" or ""
B1:E1 contains headers listing the qualities: (ie. honest, good, evil, stupid)


For each quality listed (lets say in columns K1:N1), i would like the associated names from teh above table listed.

here's an example:


A-----B--------C-------D-----E
------Honest---Good---EVil---Stupid
--------------------------------------
Joe---Yes-------------Yes
Jack ----------Yes----Yes
Sam--------------------------Yes
Jill----Yes




Now, in K1:N1, i'd like this:


K-----------L-------M------N
Honest-----Good----Evil----Stupid

Joe---------Jack----Joe----Sam
Jill------------------Jack




thanks for any help in advance

 

[Domenic]

Try...

K2, confirmed with CONTROL+SHIFT+ENTER, and copied across and down:

=IF(ROWS(K$2:K2)<=COUNTIF(INDEX($B$2:$E$5,0,MATCH(K$1,$B$1:$E$1,0)),"Yes"),INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$E$5,0,MATCH(K$1,$B$1:$E$1,0))="Yes",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS(K$2:K2))),"")

Hope this helps!

 

[MickG]

Hi Try:-

Dim Rng As Range, Dn As Range
Set Rng = Range(Range("A2"), Range("E" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Dn.Value = "Yes" Then
        Dn.Offset(, 9) = Cells(Dn.Row, "A").Value
    End If
Next Dn

Regards Mick

 

[sssb2000]

Try...

K2, confirmed with CONTROL+SHIFT+ENTER, and copied across and down:

=IF(ROWS(K$2:K2)<=COUNTIF(INDEX($B$2:$E$5,0,MATCH(K$1,$B$1:$E$1,0)),"Yes"),INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$E$5,0,MATCH(K$1,$B$1:$E$1,0))="Yes",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS(K$2:K2))),"")

Hope this helps!

That worked like a charm! thank you.

also, thanks MikeG!