Extract data in put in a new column

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have a problem is which I would like to extract the data in Column A
dependant on the value of column B.
Column A3:A22 contains unique names column B3:B22 contains the
numerical value. In this example I would extract the if the value of
column A if column B equals 6.
I would like to use formula approach.

Thanks
Michael
 
In C3:
=IF(ISERR(SMALL(IF($B$3:$B$22=6,ROW(INDIRECT("1:"&ROWS($B$3:$B$22)))),ROWS($1:1))),"",INDEX($A$3:$A$22,SMALL(IF($B$3:$B$22=6,ROW(INDIRECT("1:"&ROWS($B$3:$B$22)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Drag the Fill Handle to copy from C3 to C22
 
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(ROWS($1:1)<=COUNTIF(B$3:B$22,6),INDEX(A$3:A$22,SMALL(IF(B$3:B$22=6,ROW(A$3:A$22)-MIN(ROW(A$3:A$22))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff
 
Since my array formula solution is practically the twin of Biff's, there's no
need to post it.

But.....I do have a NON-array version you might be interested in.

This formula builds the list beginning in cell
D3:
=IF(COUNTIF($B$3:$B$22,6)>=ROWS($4:4),INDEX($A$3:$A$22,INDEX(SMALL(($B$3:$B$22=6)*ROW($B$3:$B$22)+($B$3:$B$22<>6)*10^99,ROWS($4:4))-ROW($B$2),0)),"")

Copy down through D22

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP
 
=IF(ISERR(SMALL(IF($B$3:$B$22=6,ROW(INDIRECT("1:"&ROWS($B$3:$B$22)))),ROWS($1:1))),"",INDEX($A$3:$A$22,SMALL(IF($B$3:$B$22=6,ROW(INDIRECT("1:"&ROWS($B$3:$B$22)))),ROWS($1:1))))
Click to expand...
This worked.
Thanks to all who responded
 
Back
Top