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