Spot on!

Exactly the funcion i was looking for!

Thank you.

BR Henrik

Ron Rosenfeld skrev:

On Wed, 12 Mar 2008 04:14:02 -0700, Henrik
wrote:

I have a column with text which I want to use as basis for choosing account
based on rules in a separate columnst.

Text in A1:
01.02 7-ELEVEN Heathrow

Rule in r1:
7-ELEVEN
Account in s1:
400

I want a formula in B1 which looks up the account number from colum s when
any part of the text string in A1 matches text in column r.


Provided there are no blanks in column R, this **array** formula should do
that:

=INDEX(S1:S100,MATCH(FALSE,ISERR(SEARCH(R1:R100,A1 )),0))

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