I am trying to create a spreadsheet that will calculate varying sizes. I need it to reference a key where a letter is an established size such as H=1984, S=672, etc. so as that that column can be changed between a series of letters and the formula will automatically convert the equation.


     A       |     B      |    C     |         D         
Type One     |     H      |    94    |   (C1/B1) = 0.047
Type Two     |     S      |   115    |   (C2/B2) = 0.171

Where as if I switch H to S the formula will automatically change the value and I receive the correct computation.

     A       |     B      |    C     |         D         
Type One     |     S      |    94    |   (C1/B1) = 0.14
Type Two     |     S      |   115    |   (C2/B2) = 0.171

Do I have to make a key section that the formula will reference from depending on what is in the cells?

  • 1
    Haven't you been able to find a similar solution here on Web Applications? – Jacob Jan Tuinstra Aug 11 '13 at 10:25

Say create a table with H in G1, S in G2, 1984 in H1 and 672 in H2. In D1:


and copy down should suit.


You need to create a lookup table where the values for H and S etc. are next to the letters. Then you can use a lookup function to retrieve the values. The lookup function can be nested in a formula.

  • Could you add some example code to your answer? – Vidar S. Ramdal Jan 8 '14 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.