lookup a value within a text string
 

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.

lookup a value within a text string
 

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)

"Henrik" wrote in message
...
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.

lookup a value within a text string
 

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

lookup a value within a text string
 

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:

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)

"Henrik" wrote in message
...
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.

lookup a value within a text string
 

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

lookup a value within a text string
 

On Wed, 12 Mar 2008 05:21:01 -0700, Henrik
wrote:

Spot on!

Exactly the funcion i was looking for!

Thank you.

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