Search Column Data and Return Multiple Values across Row

[Sam via OfficeKB.com]

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

 

[Domenic]

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($D2020)<=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($D2020)))
,"")

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much. Formula works Great!

Cheers,
Sam

 

[JMB]

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