Select a cell value from a range based on criteria

Question

I'm trying to figure out a sleeker method for determining the value of a cell based on criteria defined in a given range. To put it bluntly, I have a column for standard viscosity, standard temperature, and measured temperature. I'm needing to create a fourth column that will select the standard viscosity based on the measured temperature. For example, if measured temperature is greater than or equal to 0^oC and less than 1^oC: return standard viscosity for 0^oC.

My issue is that I have a large range and find it to tedious to create a custom expression for each range. I've created a nightmarish function that was composed of IF(AND()) statements but find this exhausting, especially if I try to create an expression for a 0-200^oC scale in single digit increments.

Here is a sample of a code that works but is quite cumbersome:

=IF(AND(G8>=$C$7,G8<$C$8),$D$7,0)+IF(AND(G8>=$C$8,G8<$C$9),$D$8,0)+....

or

=IF(AND(measured temp>=0,measured temp<1),$D$7,0)+IF(AND(measured temp>=1,measured temp<2),$D$8,0)

How could I approach this in a sleeker manner?

Answer 1

You can achieve this by the VLOOKUP function.

=VLOOKUP(G8;$C$7:$D$...;2;TRUE)

Replace the three dots with the index of the last row of your table.

The first argument of VLOOKUP is the value to search for; the second argument is the range whose first column is to be searched for the value; the third argument is the index of the column containing the values to return (within the range, so 2 means the second column within the range, which is D in your case); TRUE means that you do not want to search for an exact match, but that the column to be search is an ordered list of values defining intervals.

Edit:

With MATCH and OFFSET (guessed, not tried), if column with result values is left relative to column of criterion values:

=OFFSET($C$7;...;MATCH(G8;$C$7:$C$...;1) - 1)

Replace the first three dots with the position of the result column, relative to column C (so 1 if it's column D, and -1 if it's column B), and the second three dots with the index of the last row of your range, as above.

Edit2:

INDEX instead of OFFSET is even better, se pnuts's answer and the comment below:

=INDEX($D7:$D...;MATCH(G8;$C$7:$C$...;1))