Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
lookup or find matching number in text string | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
LOOKUP a text string created from IF statement | Excel Discussion (Misc queries) | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) |