Another idea:

Add a 3rd column to your lookup table that is the multiplier: A:I = 1, J:R
= -1

=REPLACE(D2,LEN(D2),1,VLOOKUP(RIGHT(D2),A$1:C$19,2 ))*VLOOKUP(RIGHT(D2),A$1:C$19,3)

Biff

"Marsh" wrote in message
...
I have a table of letters which correspond to single it numbers. The
letters
a thru i correspond to 1 thru 9, and j thru r correspond to 1 thru 9 as
well.
I get a table of multi digit alphnumerics in the form 4567C or 398g or
28754n, etc.
I place the letters and corresponding digits in a lookup table. I replace
the letters at the end with the corresponding number as defined above with
the formula
=SUBSTITUTE(D2,RIGHT(D2,1),VLOOKUP(RIGHT(D2,1),$A$ 1:$B$19,2))
Works fine.
What I cant figure out is if the letter is between j thru r, I need to
must
convert the formula result to a negative number.
For example, 458b becomes 4582, while 458k becomes -4582.
Have tried IF, but cant seem to get the syntax correct. Possibly some
sort
of array formula might help, but cant get that either.
Any suggestions???
Thanks, Marsh