H and V lookup or match?
in [Functions]
|
Prev: my paste special function won't let me copy formulas
Next: Inserting a calculated field in a Pivot Table in Excel 2003
From: excelrookie on 6 Apr 2010 16:49 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
From: Teethless mama on 6 Apr 2010 19:30 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 "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 >
From: Bernard Liengme on 6 Apr 2010 19:40
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" <excelrookie(a)discussions.microsoft.com> wrote in message news:D9CC593F-352B-4FA4-88D8-8EA47A246471(a)microsoft.com... > 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 > |