Hi,
I have an array where some of the cells contain strings and some are blank.
I am looking for a formula that finds a string from the array within another
string and resualts the string found.

e.g find within the array below a string in "ADFGBBC IIO" == result should
be "BBC"
===
ABC

CC
BBC
===


thanks

Suppose you have these values in a range of cells:

D1 = ABC
D2 = (empty)
D3 = CC
D4 = BBC

A1 = ADFGBBC IIO

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

=INDEX(D1:D4,MATCH(TRUE,ISNUMBER(SEARCH(IF(D1:D4< "",D1:D4),A1)),0))

Biff

"Nir" wrote in message
...
Hi,
I have an array where some of the cells contain strings and some are
blank.
I am looking for a formula that finds a string from the array within
another
string and resualts the string found.

e.g find within the array below a string in "ADFGBBC IIO" == result
should
be "BBC"
===
ABC

CC
BBC
===


thanks

Biff
It works, you saved me hell of a time (not sure i understand how it
works.......)

thanks



"Biff" wrote:

Suppose you have these values in a range of cells:

D1 = ABC
D2 = (empty)
D3 = CC
D4 = BBC

A1 = ADFGBBC IIO

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

=INDEX(D1:D4,MATCH(TRUE,ISNUMBER(SEARCH(IF(D1:D4< "",D1:D4),A1)),0))

Biff

"Nir" wrote in message
...
Hi,
I have an array where some of the cells contain strings and some are
blank.
I am looking for a formula that finds a string from the array within
another
string and resualts the string found.

e.g find within the array below a string in "ADFGBBC IIO" == result
should
be "BBC"
===
ABC

CC
BBC
===


thanks

Biff
i would like to use it in case there are no space in the string.
use the example below if:
D1=II
D2=IO
D3=IIO

so formula resualt will be only "IIO"

thanks


"Biff" wrote:

Suppose you have these values in a range of cells:

D1 = ABC
D2 = (empty)
D3 = CC
D4 = BBC

A1 = ADFGBBC IIO

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

=INDEX(D1:D4,MATCH(TRUE,ISNUMBER(SEARCH(IF(D1:D4< "",D1:D4),A1)),0))

Biff

"Nir" wrote in message
...
Hi,
I have an array where some of the cells contain strings and some are
blank.
I am looking for a formula that finds a string from the array within
another
string and resualts the string found.

e.g find within the array below a string in "ADFGBBC IIO" == result
should
be "BBC"
===
ABC

CC
BBC
===


thanks

Nir wrote...
Biff
i would like to use it in case there are no space in the string.
use the example below if:
D1=II
D2=IO
D3=IIO

so formula resualt will be only "IIO"

Difficult. D1 = "II" and D2 = "IO" are substrings of D3 = "IIO". You'd
need to set up your search so it located IIO first, then either II or
IO. So if you're searching for any of these substrings in a string
named x, try

=LOOKUP(1000000,SEARCH(D1:D3,x),D1:D3)

which doesn't require array entry.

Try this array formula:

=INDEX($D$D, ROUND( 1 / MOD( MIN( LEN( SUBSTITUTE( A1, D1:D4, "" ) ) + 1 /
ROW( D1:D4) ), 1 ), 0 ) )
--
Regards,
Luc.

"Festina Lente"


"Nir" wrote:

Biff
i would like to use it in case there are no space in the string.
use the example below if:
D1=II
D2=IO
D3=IIO

so formula resualt will be only "IIO"

thanks


"Biff" wrote:

Suppose you have these values in a range of cells:

D1 = ABC
D2 = (empty)
D3 = CC
D4 = BBC

A1 = ADFGBBC IIO

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

=INDEX(D1:D4,MATCH(TRUE,ISNUMBER(SEARCH(IF(D1:D4< "",D1:D4),A1)),0))

Biff

"Nir" wrote in message
...
Hi,
I have an array where some of the cells contain strings and some are
blank.
I am looking for a formula that finds a string from the array within
another
string and resualts the string found.

e.g find within the array below a string in "ADFGBBC IIO" == result
should
be "BBC"
===
ABC

CC
BBC
===


thanks

Sorry, I made some mistakes while copying the formula, here it is (I hope):

=INDEX($D:$D, ROUND( 0.1 / MOD( MIN( LEN( SUBSTITUTE( A1, D1:D4, "" ) ) +
0.1 / ROW( D1:D4 ) ), 1 ), 0 ) )

--
Regards,
Luc.

"Festina Lente"


"PapaDos" wrote:

Try this array formula:

=INDEX($D$D, ROUND( 1 / MOD( MIN( LEN( SUBSTITUTE( A1, D1:D4, "" ) ) + 1 /
ROW( D1:D4) ), 1 ), 0 ) )
--
Regards,
Luc.

"Festina Lente"


"Nir" wrote:

Biff
i would like to use it in case there are no space in the string.
use the example below if:
D1=II
D2=IO
D3=IIO

so formula resualt will be only "IIO"

thanks


"Biff" wrote:

Suppose you have these values in a range of cells:

D1 = ABC
D2 = (empty)
D3 = CC
D4 = BBC

A1 = ADFGBBC IIO

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

=INDEX(D1:D4,MATCH(TRUE,ISNUMBER(SEARCH(IF(D1:D4< "",D1:D4),A1)),0))

Biff

"Nir" wrote in message
...
Hi,
I have an array where some of the cells contain strings and some are
blank.
I am looking for a formula that finds a string from the array within
another
string and resualts the string found.

e.g find within the array below a string in "ADFGBBC IIO" == result
should
be "BBC"
===
ABC

CC
BBC
===


thanks