Return search value, not starting position
 

Hello all,

I am having great difficulty with this one. I am using an array
search to find the first instance of a value from a list in a text
cell. For example:

={min(if(iserror(search(A1:A5,B1,0),500,search(A1: A5,B1,0)))}

A1 Fast
A2 Fascinating
A3 Hello
A4 Helium
A5 Row

Where B1=Whateverthisisfastbutnotfascinating

This works in giving me the value; in this case it would be 15 as
"Fast" starts in the 15th character. But what I need to know is what
the value is that it found first. Can anyone help me?

I have made it to work, but only if there is a " " or "," between
words. I want to be able to make it work in a text string with no
spaces, but I don't know how to either: 1. return the value rather
than the starting position, or 2. a way to know the number of
characters from what it matched (in this case that would be 4 for
"Fast").

If anyone can help me solve this, I'd be most impressed!

Thanks,
Jamison

Return search value, not starting position
 

Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
instead of just ENTER:

=INDEX(A1:A5,MATCH(MIN(IF(COUNTIF(B1,"*"&A1:A5&"*" ),
SEARCH(A1:A5,B1))),SEARCH(A1:A5,B1)))

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

wrote in message
...
Hello all,

I am having great difficulty with this one. I am using an array
search to find the first instance of a value from a list in a text
cell. For example:

={min(if(iserror(search(A1:A5,B1,0),500,search(A1: A5,B1,0)))}

A1 Fast
A2 Fascinating
A3 Hello
A4 Helium
A5 Row

Where B1=Whateverthisisfastbutnotfascinating

This works in giving me the value; in this case it would be 15 as
"Fast" starts in the 15th character. But what I need to know is what
the value is that it found first. Can anyone help me?

I have made it to work, but only if there is a " " or "," between
words. I want to be able to make it work in a text string with no
spaces, but I don't know how to either: 1. return the value rather
than the starting position, or 2. a way to know the number of
characters from what it matched (in this case that would be 4 for
"Fast").

If anyone can help me solve this, I'd be most impressed!

Thanks,
Jamison

Return search value, not starting position
 

Glad to see an easy question :-) Okay, here's one solution:

1. Add a column - e.g Column C that is an array formula (or just a regular
formula) that returns the Search result:

e.g. C1:C5 would be {=IF(ISERROR(SEARCH(A1:A5,B1,1)),500,SEARCH(A1:A5, B1,1))}

2. Now in the cell you want to echo the first word found, enter the single
array formula:

e.g. D1 would be {=IF(C1:C5=MIN(C1:C5),A1:A5)}

D1 would then return the value Fast. There might be a way to put this into
one array formula, but it's late and my mind isn't up to this right now. :-)
Hopefully you can use this (or help you rewrite it into one formula).

" wrote:

Hello all,

I am having great difficulty with this one. I am using an array
search to find the first instance of a value from a list in a text
cell. For example:

={min(if(iserror(search(A1:A5,B1,0),500,search(A1: A5,B1,0)))}

A1 Fast
A2 Fascinating
A3 Hello
A4 Helium
A5 Row

Where B1=Whateverthisisfastbutnotfascinating

This works in giving me the value; in this case it would be 15 as
"Fast" starts in the 15th character. But what I need to know is what
the value is that it found first. Can anyone help me?

I have made it to work, but only if there is a " " or "," between
words. I want to be able to make it work in a text string with no
spaces, but I don't know how to either: 1. return the value rather
than the starting position, or 2. a way to know the number of
characters from what it matched (in this case that would be 4 for
"Fast").

If anyone can help me solve this, I'd be most impressed!

Thanks,
Jamison