I am working with an inventory list and need to come up with formula to set a sale price of an item based on a text description. I've fooled around with this for a while and have only hit dead ends. So now hopefully you Excel guru's can lend a hand.

A1 is the text description of the item, A2 is the retail value and A3 is the formula cell.

A1
Canada Scott 158 Used

A2
$150.00

The last word of A1 changes, as does the sale price. A3 is the sale price which is a percentage of A2.

if A1 contains the word "Used" then A3 equals 50% of A2
"Faulty" 10%
"MNG" 60%
"MH" 65%
"MLH" 75%
"MNH" 85%

Thank-you in advance, and hopefully my description makes sense.

Cheers, Greg