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)