Extract data in put in a new column
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 |
Extract data in put in a new column
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 |
Extract data in put in a new column
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 |
Extract data in put in a new column
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 |
Extract data in put in a new column
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 |
Extract data in put in a new column
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 |
All times are GMT +1. The time now is 04:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com