H and V lookup or match?
 

If I have the following data

A B C D E
NY MO IL NJ KS

1 Jim x x x
2 Joe x x x x
3 Amy x x x
4 Jane x x x x

How can I write a function that if I input NY into F1 the answer "Jim, Joe,
Jane" will show up in G1 and also if nothing is in F1 is will be blank?

Thanks

H and V lookup or match?
 

Assuming your data in A1:F5

NY MO IL NJ KS
Jim x x x
Joe x x x x
Amy x x x
Jane x x x x

Criteria in G1

In H1:
=LOOKUP("zzzz",CHOOSE({1,2},"",INDEX($A$2:$A$5,SMA LL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIREC T("1:"&ROWS($A$2:$A$5)))),ROWS($1:1)))))

ctrl+shift+enter, not just enter
copy down as far as needed


"excelrookie" wrote:

If I have the following data

A B C D E
NY MO IL NJ KS

1 Jim x x x
2 Joe x x x x
3 Amy x x x
4 Jane x x x x

How can I write a function that if I input NY into F1 the answer "Jim, Joe,
Jane" will show up in G1 and also if nothing is in F1 is will be blank?

Thanks

H and V lookup or match?
 

Another suggestion
Assuming data in A1:F5; criteria state code in G1
In H1: =MATCH(G1,B1:F1,0)
In I1: =IF(INDEX(B2:F5,1,H1)="x",A2&", ","")&IF(INDEX(B2:F5,2,H1)="x",A3&",
","")&IF(INDEX(B2:F5,3,H1)="x",A4&", ","")&IF(INDEX(B2:F5,4,H1)="x",A5,"")
Not as elegant as Teethless Mama's but also not an array formula
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"excelrookie" wrote in message
...
If I have the following data

A B C D E
NY MO IL NJ KS

1 Jim x x x
2 Joe x x x x
3 Amy x x x
4 Jane x x x x

How can I write a function that if I input NY into F1 the answer "Jim,
Joe,
Jane" will show up in G1 and also if nothing is in F1 is will be blank?

Thanks