Search Column Data and Return Multiple Values across Row
 

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.a...tions/200609/1

Search Column Data and Return Multiple Values across Row
 

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@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

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

Search Column Data and Return Multiple Values across Row
 

Hi Domenic,

Thank you very much. Formula works Great!

Cheers,
Sam

Domenic wrote:
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

Search Column Data and Return Multiple Values across Row
 

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:

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.a...tions/200609/1