Excel Forumla to seach and return all values
 

Someone has [kindly] offered this solution to the problem I mention at
the bottom of this note...

=IF(COUNTIF($B$5:$B$1000,"*CL*"),VLOOKUP("*CL*",$B $5:$B$1000,1,0),"")

which kinda works, however on dragging the formula down it still only
gives me back the first value that meets the criteria it comes
across...not all of them...
eg:
CL_0001
CL_0002

ie: although another value exists, it only gives me back CL_0001

Any ideas? I'm getting desperate! and would really appreciate some help

cheers
Sue

__________________________________________________ ______________

I would like the formula below to retrieve and return any entry within
a list (in excel) that begins with [or contains] the text "CL"..(in
this instance) - if there is nothing it returns a blank..

=IF(ISNUMBER(SEARCHB("CL",B5,1)),B5,"")

However I can only get it to search 1 cell at a time (and only in cells
below that cell ref (ie: B5), not above it). Is it possible to replace
the cell value with a range (ie: B:B)...so it searches more
extensively?

or is there something better that will do the trick?
__________________________________________________ ______________

Hi
the you need an array formula (entered with cTRL+SHIFT+ENTER). e.g,. in C1
enter:
=INDEX($B$5:$B$1000,SMALL(IF(ISNUMBER(SEARCH("CL", $B$5:$B$1000)),ROW($B$5:$B$1000)),ROW()))
and copy this down

" wrote:

Someone has [kindly] offered this solution to the problem I mention at
the bottom of this note...

=IF(COUNTIF($B$5:$B$1000,"*CL*"),VLOOKUP("*CL*",$B $5:$B$1000,1,0),"")

which kinda works, however on dragging the formula down it still only
gives me back the first value that meets the criteria it comes
across...not all of them...
eg:
CL_0001
CL_0002

ie: although another value exists, it only gives me back CL_0001

Any ideas? I'm getting desperate! and would really appreciate some help

cheers
Sue

__________________________________________________ ______________

I would like the formula below to retrieve and return any entry within
a list (in excel) that begins with [or contains] the text "CL"..(in
this instance) - if there is nothing it returns a blank..

=IF(ISNUMBER(SEARCHB("CL",B5,1)),B5,"")

However I can only get it to search 1 cell at a time (and only in cells
below that cell ref (ie: B5), not above it). Is it possible to replace
the cell value with a range (ie: B:B)...so it searches more
extensively?

or is there something better that will do the trick?
__________________________________________________ ______________

Hi Frank

Many many thanks for that! you've saved me!...one last question...can I
make this an iserror forumla, so that if/when it returns #NUM!, the
cell is blank??
I'm not sure if you can use an iserror with an array??

I've tried:

=IF(ISERROR(Your formula above),"",(Your formula above))

but it doesn't like the "", even when i replace that with another
value, ie: 0..

Any ideas?

cheers
Sue

Sorry - please ignore my note below..I must have been doing something
daft...it [formula below] now works...Thank you again!! you are a
saviour!

cheers
Sue


={IF(ISERROR(INDEX('Pivot
40%'!$B$5:$B$1000,SMALL(IF(ISNUMBER(SEARCHB("CL", 'Pivot
40%'!$B$5:$B$1000,1)),ROW('Pivot
40%'!$B$5:$B$1000)),ROW()))),"",(INDEX('Pivot
40%'!$B$5:$B$1000,SMALL(IF(ISNUMBER(SEARCHB("CL", 'Pivot
40%'!$B$5:$B$1000,1)),ROW('Pivot 40%'!$B$5:$B$1000)),ROW()))))}

Sorry - please ignore my note below..I must have been doing something
daft...it [formula below] now works...Thank you again!! you are a
saviour!

cheers
Sue


={IF(ISERROR(INDEX('Pivot
40%'!$B$5:$B$1000,SMALL(IF(ISNUMBER(SEARCHB("CL", 'Pivot
40%'!$B$5:$B$1000,1)),ROW('Pivot
40%'!$B$5:$B$1000)),ROW()))),"",(INDEX('Pivot
40%'!$B$5:$B$1000,SMALL(IF(ISNUMBER(SEARCHB("CL", 'Pivot
40%'!$B$5:$B$1000,1)),ROW('Pivot 40%'!$B$5:$B$1000)),ROW()))))}

Hi sue
thanks for the feedback and glad you sorted it out :-)

" wrote:

Sorry - please ignore my note below..I must have been doing something
daft...it [formula below] now works...Thank you again!! you are a
saviour!

cheers
Sue


={IF(ISERROR(INDEX('Pivot
40%'!$B$5:$B$1000,SMALL(IF(ISNUMBER(SEARCHB("CL", 'Pivot
40%'!$B$5:$B$1000,1)),ROW('Pivot
40%'!$B$5:$B$1000)),ROW()))),"",(INDEX('Pivot
40%'!$B$5:$B$1000,SMALL(IF(ISNUMBER(SEARCHB("CL", 'Pivot
40%'!$B$5:$B$1000,1)),ROW('Pivot 40%'!$B$5:$B$1000)),ROW()))))}

Oh dear...You know what - and I knew I'd do it..in all my excitement, I
spoke to soon!
The formula is doing the right thing, but returning the wrong values...

I am asking it to seach for anything beginning/containing CL and
instead, it is returning me values starting with CF etc (and only some
of them...)?

If you copy the values in the list below into cells B5:B29 and then
paste the formula below the list into C1, you'll see what I mean...

CL_0015
CL_0021
CL_0022
CL_0025
CL_0028
CL_0029
CL_0030
CF_0002
CF_0003
CF_0008
CF_0009
CF_0019
CF_0027
CS_0034
CS_0038
EO_0002
FI_0001
FI_0002
FI_0012
FI_0013
IF_0009
NI_0001
NI_0007
NI_0008
NI_0009

{=IF(ISERROR(INDEX(B5:B29,SMALL(IF(ISNUMBER(SEARCH B("CL",B5:B29,1)),ROW(B5:B29)),ROW()))),"",(INDEX( B5:B29,SMALL(IF(ISNUMBER(SEARCHB("CL",B5:B29,1)),R OW(B5:B29)),ROW()))))}

No idea this time...but again, appreciate your genius here!
many thanks
Sue

Hi
you have to use absoulte references. That is insetad of
B1:B100
use $B$1:$B$100
as provided in my initial example

--
Regards
Frank Kabel
Frankfurt, Germany
schrieb im Newsbeitrag
ups.com...
Oh dear...You know what - and I knew I'd do it..in all my excitement, I
spoke to soon!
The formula is doing the right thing, but returning the wrong values...

I am asking it to seach for anything beginning/containing CL and
instead, it is returning me values starting with CF etc (and only some
of them...)?

If you copy the values in the list below into cells B5:B29 and then
paste the formula below the list into C1, you'll see what I mean...

CL_0015
CL_0021
CL_0022
CL_0025
CL_0028
CL_0029
CL_0030
CF_0002
CF_0003
CF_0008
CF_0009
CF_0019
CF_0027
CS_0034
CS_0038
EO_0002
FI_0001
FI_0002
FI_0012
FI_0013
IF_0009
NI_0001
NI_0007
NI_0008
NI_0009

{=IF(ISERROR(INDEX(B5:B29,SMALL(IF(ISNUMBER(SEARCH B("CL",B5:B29,1)),ROW(B5:B29)),ROW()))),"",(INDEX( B5:B29,SMALL(IF(ISNUMBER(SEARCHB("CL",B5:B29,1)),R OW(B5:B29)),ROW()))))}

No idea this time...but again, appreciate your genius here!
many thanks
Sue