lookup a value within a text string

[Henrik]

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.

 

[Ron Coderre]

Try this:
B1: =IF(COUNTIF(A1,"*"&R1&"*"),S1,"no match")

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

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Rosenfeld]

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

 

[Henrik]

Thankyou!
I do not know how to expand to look further in the rows below r1 and s1. I
have about 100 rows and therefore nested if statements would not suffice.

BR

Henrik

Ron Coderre skrev:

 

[Henrik]

Spot on!

Exactly the funcion i was looking for!

Thank you.

BR Henrik

Ron Rosenfeld skrev:

 

[Ron Rosenfeld]

Spot on!

Exactly the funcion i was looking for!

Thank you.

Glad to help. Thanks for the feedback.
--ron