FIND MULTIPLE TEXT FROM DATA
Hi,
I want to find multiple text from a set of data and show the data of some other cell if the data is found. Eg: Cell A1 contains data - Business class Cell B2 contains Amount In Cell C3 need a function which will show data of cell B2 if the text "Business is found. I have used IF function and am able to get the same. But, I was not successful in framing a single formula which could find multiple text from the data and give me the above result. Would be great if could you help me with this. Thanks, Amit |
FIND MULTIPLE TEXT FROM DATA
Try something like this:
With this list E1: Business E2: Cab E3: Taxi And.....these values in A1:B4 Business Class__10 Coach___________20 Taxi Cab________30 Business Class__40 Then.... C1: =MAX(INDEX(COUNTIF(A1,"*"&$E$1:$E$3&"*"),0))*B1 Copy that formula down through C4 In the above example....the formulas return these values: C1: 10 C2: 0 C3: 30 C4: 40 Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "AK" wrote in message ... Hi, I want to find multiple text from a set of data and show the data of some other cell if the data is found. Eg: Cell A1 contains data - Business class Cell B2 contains Amount In Cell C3 need a function which will show data of cell B2 if the text "Business is found. I have used IF function and am able to get the same. But, I was not successful in framing a single formula which could find multiple text from the data and give me the above result. Would be great if could you help me with this. Thanks, Amit |
FIND MULTIPLE TEXT FROM DATA
Hi Ron,
Thanks for the solution, one more problem, your formula is not effective if there is a typo in the base data E.g.: If Business Class has been entered as Businessclass in the base data, then the formula fails to identify the text business. Any solution, as the data I am working with has lots of error like one shown above. "Ron Coderre" wrote: Try something like this: With this list E1: Business E2: Cab E3: Taxi And.....these values in A1:B4 Business Class__10 Coach___________20 Taxi Cab________30 Business Class__40 Then.... C1: =MAX(INDEX(COUNTIF(A1,"*"&$E$1:$E$3&"*"),0))*B1 Copy that formula down through C4 In the above example....the formulas return these values: C1: 10 C2: 0 C3: 30 C4: 40 Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "AK" wrote in message ... Hi, I want to find multiple text from a set of data and show the data of some other cell if the data is found. Eg: Cell A1 contains data - Business class Cell B2 contains Amount In Cell C3 need a function which will show data of cell B2 if the text "Business is found. I have used IF function and am able to get the same. But, I was not successful in framing a single formula which could find multiple text from the data and give me the above result. Would be great if could you help me with this. Thanks, Amit |
FIND MULTIPLE TEXT FROM DATA
In my testing, the formula matched the following from A1:
Businesssss businessclass taxibusiness taxiCabbusiness Perhaps if you posted more information about your situation. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "AK" wrote in message ... Hi Ron, Thanks for the solution, one more problem, your formula is not effective if there is a typo in the base data E.g.: If Business Class has been entered as Businessclass in the base data, then the formula fails to identify the text business. Any solution, as the data I am working with has lots of error like one shown above. "Ron Coderre" wrote: Try something like this: With this list E1: Business E2: Cab E3: Taxi And.....these values in A1:B4 Business Class__10 Coach___________20 Taxi Cab________30 Business Class__40 Then.... C1: =MAX(INDEX(COUNTIF(A1,"*"&$E$1:$E$3&"*"),0))*B1 Copy that formula down through C4 In the above example....the formulas return these values: C1: 10 C2: 0 C3: 30 C4: 40 Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "AK" wrote in message ... Hi, I want to find multiple text from a set of data and show the data of some other cell if the data is found. Eg: Cell A1 contains data - Business class Cell B2 contains Amount In Cell C3 need a function which will show data of cell B2 if the text "Business is found. I have used IF function and am able to get the same. But, I was not successful in framing a single formula which could find multiple text from the data and give me the above result. Would be great if could you help me with this. Thanks, Amit |
All times are GMT +1. The time now is 05:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com