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

Hi Michael

Try
=INDEX(A3:A22,MATCH(6,B3:B22,0))

--
Regards

Roger Govier


"Michael" wrote in message
...
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


"Michael" wrote:

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

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

"Michael" wrote in message
...
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

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


"Michael" wrote:

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

On Sat, 17 Feb 2007 13:25:38 -0800, Teethless mama
wrote:

=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))))
This worked.
Thanks to all who responded