I need to fill the \"Evaporator Temperature\" column with temperatures (selected

Question

I need to fill the "Evaporator Temperature" column with temperatures (selected from the table on the right) that correspond to the System Refrigerant in cell K3. I can accomplish this using the formula

=INDEX(D$3:D$23,MATCH(A3,G$3:G$23,1))

But, I am constantly adding new refrigerants to the table and I need to have the "Evaporator Temperature" column update automatically when I enter a different system refrigerant into cell K3. With the formula above, I have to change the 'lookup_array' so that it corresponds the the correct refrigerant. Is there any way I can make the 'lookup_array' automatically switch to the corresponding refrigerant?

Evaporator Evaporator Temperature R404a R454b R455a 2 Pressure Temperature 3 21.196 423.97 527.019 6 30.361 7 34.013 8 37.995 9 42.325 10 47.023 11 52.11 12 57.604 13 63.526 14 69.898 15 76.741 16 84.075 17 91.923 18 100.31 19 109.25 20 118.77 21 128.9 22 138.67 23 System Refrigerant R455a 15.17118.716 19.729 17.28521.331 22.264 19.622 24.22625.044 22.19827.42 28.085 25.03130.933 31.404 28.135 34.789 35.015 31.529 39.007 38.935 35.23 43.611 43.182 39.25648.624 47.771 43.62554.06952.72 48.35559.97 58.046 53.46766.352 63.767 58.97973.24 64.911 80.66 76.464 71.28488.637 83.475 78.118 97.199 90.953 85.434106.37 98.915 93.255116.18 107.38 101.6 126.66 116.37 110.5 137.84 125.89 119.96 149.74 135.97 -45 35 25 20 15 10 10 15 20 25 69.9 35 45 25 27 31

Explanation / Answer

You can provide the indirect value in the column

for example: =INDIRECT (D1) referst to the value in D1

And =CONCATENATE(d1,3)concatenates the string in d1 and 3

and combining both we can derive a formula

=INDEX(D$3:D$23,MATCH(A3,INDIRECT(CONCATENATE(K3,3)):INDIRECT(CONCATENATE(K3,23)),1))

By using this formuls you have to enter the column header or column name in K3 cell

Even you can change the name of the clumn

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---