Hi ASHA27,

You can use left within a LOOKUP or VLOOKUP formula but you either need
a separate table to show HAC0, HAC4 etc and the corresponding values or
you'll have to include all those in the formula, the first option (a
table) is normally the best, e.g. if you have your "HAC0" etc in C1
down and the corresponding multiplier in D1 down

=VLOOKUP(LEFT(A2),C:D,2,0)*B2

If all your text in column A begins with "HAC" perhaps you could simply
look at the 4th character...

=VLOOKUP(MID(A2,4,1)+0,C:D,2,0)*B2


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516565