H and V lookup or match?

E

excelrookie

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
 
T

Teethless mama

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,SMALL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIRECT("1:"&ROWS($A$2:$A$5)))),ROWS($1:1)))))

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

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top