Searching though several columns and tables

[ijhoeq]

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?

 

[steve the fish]

Try changing the lookup array to this:

INDEX($E$3:$G$23,,MATCH($K$3,$E$2:$G$2,0))

 

[ijhoeq]

I've updated it to

=INDEX(D$3:D$23,MATCH($K$3,INDEX($E$3:$G$23,MATCH($K$3,$E$2:$G$2,0))))

and I get a #REF !

 

[steve the fish]

You replace G$3:G$23 in your formula with what i put. Change nothing else and you should be fine.

 

[ijhoeq]

Thanks a lot! It works great!