Return search value, not starting position

[jamison.folland]

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

 

[Ron Coderre]

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

 

[BobT]

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).