Fishboy
Well-known Member
- Joined
- Feb 13, 2015
- Messages
- 4,267
Hi All,
I have been toying with some Excel 2010 data and I am trying to perform a lookup if the lookup value is between 2 other values. I have given an example of the lookup table below:
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Now, the code I have tried to doctor from the interwebs (where the cell reference to look up is I6) is as follows:
=SUMPRODUCT((I6>=Chart!A2:A6)*(I6<=Chart!B2:B6)*Chart!C2:C6)
It was my understanding that the above would check if I6 is greater than A2:A6 and less than B2:B6 to give the value in the corresponding cell from C2:C6, however all I am getting is #VALUE!
Can anyone spot what I am doing wrong, or suggest an alternative way to lookup between 2 values?
I have been toying with some Excel 2010 data and I am trying to perform a lookup if the lookup value is between 2 other values. I have given an example of the lookup table below:
Excel 2010
A | B | C | |
---|---|---|---|
1 | Min | Max | Output |
2 | 1 | 14 | N/A |
3 | 15 | 29 | TEST1 |
4 | 30 | 52 | TEST2 |
5 | 53 | 95 | TEST3 |
6 | 96 | 100 | TEST4 |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Chart
Now, the code I have tried to doctor from the interwebs (where the cell reference to look up is I6) is as follows:
=SUMPRODUCT((I6>=Chart!A2:A6)*(I6<=Chart!B2:B6)*Chart!C2:C6)
It was my understanding that the above would check if I6 is greater than A2:A6 and less than B2:B6 to give the value in the corresponding cell from C2:C6, however all I am getting is #VALUE!
Can anyone spot what I am doing wrong, or suggest an alternative way to lookup between 2 values?