On Tue, 20 Sep 2005 13:41:18 -0400, Ron Rosenfeld
wrote:

On Tue, 20 Sep 2005 10:16:40 -0500, tobriant
wrote:


Is it possible to look up a string of text within another string of
text, using some variation of vlookup? For example, I may need to find
a text string like "First Bank" within a column containing strings of
text that may be "The First Bank" or "My First Bank" or some variation
containing "First Bank." Preferably, the lookup would not be case
sensitive.

Any suggestions?

This **array** formula will tell you which is the first item that matches your
criteria. You can then use that in an Index function to return the rest of the
data.

For example:

=INDEX(rng,MATCH(TRUE,ISNUMBER(SEARCH("first bank",rng))))

will return the entire string in which "first bank" was found. Similar
functions will return other columnar data.

The SEARCH function is case-insensitive.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron

TYPO alert! Formula should be:

=INDEX(rng,MATCH(TRUE,ISNUMBER(SEARCH("last",rng)) ,0))


--ron