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