H and V lookup or match?

  • Thread starter Thread starter excelrookie
  • Start date Start date
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
 
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
 
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
 
Back
Top