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

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

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