Some Positive, Some Negative
 

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

Some Positive, Some Negative
 

One way:
=SUBSTITUTE(D2,RIGHT(D2),VLOOKUP(RIGHT(D2),$A$1:$B $19,2))*IF(ISNUMBER(MATCH(RIGHT(D2),$A$10:$A$19,0) ),-1,1)



"Marsh" wrote:

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

Some Positive, Some Negative
 

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

Some Positive, Some Negative
 

Thank you both for showing me two differenct approaches to a solution to my
problem. Glad you were able to help and that you allowed me to pick your
brains.
Marsh

"Marsh" wrote:

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