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

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

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

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