Need a formula

Posted by Chet on July 30, 2001 8:35 PM

I am working on a pricing spread sheet an I would like to know how to assign a value to a 2 letter designation code? Example if a cell has HR in it how can I make another cell recognize that value as $20.00?

As a follow up to the first question, is it possible to have that cell recognize multiple 2 letter designation codes and apply the appropriate dollar amount.

Posted by Aladin Akyurek on July 31, 2001 12:08 AM

Make a 2-column table on a separate worksheet. Lets say that you enter all 2-letter codes in A from A2 on and the corresponding dollar amounts in B from B2 on.

Select all of the cells of the 2-column table and name it (e.g.,) CTABLE via the Name Box.

Now suppose that we have on some other worksheet the following:

A6 contains HR and
B6 contains some quantity.

In C6 enter: =B6*VLOOKUP(A6,CTABLE,2,0)

will give you the total amount.

Aladin