I am trying to create a lookup table for a calculator I am creating in excel.
I need the calculator to look up a deration factor based on both the
altitude and the temperature. For instance, if I put in a temperature that
falls in the range of 50 to 60 degrees fahrenheit and a altitude falls in the
range of 0 to 1000 feet, I need excel to tell me that my deration factor is
1.00. However, if I put in a temperature that is above 130 with an altitude
of 3000 to 4000 feet I need Excel to tell me that my deration is 0.98. Is
there a method or formula that would allow me to accomplish this task?

Build a table with temperature as row headers (lets say A2:A4), range as
column headers (say B1:D1). Example:

0 1001 2001
50 1 2 3
61 4 5 6
71 7 8 9

Now, if your temp is called out in E1, and altitude in E2:
=INDEX(B2:D4,MATCH(E1,A2:A4),MATCH(E2,B1:D1))

Note that each label is the lower boundary of the group, so in example, 5
would be the value for temp range of 61-70, and altitude of 1001-200.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Peri" wrote:

I am trying to create a lookup table for a calculator I am creating in excel.
I need the calculator to look up a deration factor based on both the
altitude and the temperature. For instance, if I put in a temperature that
falls in the range of 50 to 60 degrees fahrenheit and a altitude falls in the
range of 0 to 1000 feet, I need excel to tell me that my deration factor is
1.00. However, if I put in a temperature that is above 130 with an altitude
of 3000 to 4000 feet I need Excel to tell me that my deration is 0.98. Is
there a method or formula that would allow me to accomplish this task?