Search Column Data and Return Multiple Values across Row "Sam via OfficeKB.com" <u4102[ at ]uwe> 30.09.2006 16:39:13 Hi All, I have two columns of data. Column "A" houses Numeric Labels and Column "B" houses Numeric Values. Data: Numeric Labels A20:A105 Numeric Values B20:B105 I would like to return across a Single Row all Numeric Lables that have a corresponding Numeric Value in Column "B" that is >=2 (greater than or equal to 2). Thanks, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200609/1

Re: Search Column Data and Return Multiple Values across Row Domenic <domenic22[ at ]sympatico.ca> 30.09.2006 17:36:53 Assuming that the results are to be returned in Row 20, starting at D20, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... D20, copied across: =IF(COLUMNS($D20:D20)<=COUNTIF($B$20:$B$105,">=2"),INDEX($A$20:$A$105,SMA LL(IF($B$20:$B$105>=2,ROW($B$20:$B$105)-ROW($B$20)+1),COLUMNS($D20:D20))) ,"") Hope this helps! In article <671292b883da7[ at ]uwe>, "Sam via OfficeKB.com" <u4102[ at ]uwe> wrote: [Quoted Text] > Hi All, > > I have two columns of data. Column "A" houses Numeric Labels and Column "B" > houses Numeric Values. > > Data: > Numeric Labels A20:A105 > Numeric Values B20:B105 > > I would like to return across a Single Row all Numeric Lables that have a > corresponding Numeric Value in Column "B" that is >=2 (greater than or equal > to 2). > > Thanks, > Sam

Re: Search Column Data and Return Multiple Values across Row "Sam via OfficeKB.com" <u4102[ at ]uwe> 30.09.2006 18:03:58 Hi Domenic, Thank you very much. Formula works Great! Cheers, Sam Domenic wrote: [Quoted Text] >Assuming that the results are to be returned in Row 20, starting at D20, >try the following formula which needs to be confirmed with >CONTROL+SHIFT+ENTER... >D20, copied across: >=IF(COLUMNS($D20:D20)<=COUNTIF($B$20:$B$105,">=2"),INDEX($A$20:$A$105,SMA >LL(IF($B$20:$B$105>=2,ROW($B$20:$B$105)-ROW($B$20)+1),COLUMNS($D20:D20))) >,"") >Hope this helps! -- Message posted via http://www.officekb.com

RE: Search Column Data and Return Multiple Values across Row JMB 30.09.2006 18:50:01 Just an FYI at this point - you also could have used the autofilter and filtered on column B (custom) for values >= 2. Then select the values in column A and copy, select your destination, then click Edit/Paste Special - Transpose "Sam via OfficeKB.com" wrote: [Quoted Text] > Hi All, > > I have two columns of data. Column "A" houses Numeric Labels and Column "B" > houses Numeric Values. > > Data: > Numeric Labels A20:A105 > Numeric Values B20:B105 > > I would like to return across a Single Row all Numeric Lables that have a > corresponding Numeric Value in Column "B" that is >=2 (greater than or equal > to 2). > > Thanks, > Sam > > -- > Message posted via OfficeKB.com > http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200609/1 > >