Using sumproduct for lookup

[Fishboy]

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

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]Output[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #F0F0F0, align: center"]1[/TD]
[TD="bgcolor: #F0F0F0, align: center"]14[/TD]
[TD="bgcolor: #F0F0F0, align: center"]N/A[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #D9D9CD, align: center"]15[/TD]
[TD="bgcolor: #D9D9CD, align: center"]29[/TD]
[TD="bgcolor: #D9D9CD, align: center"]TEST1[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #F0F0F0, align: center"]30[/TD]
[TD="bgcolor: #F0F0F0, align: center"]52[/TD]
[TD="bgcolor: #F0F0F0, align: center"]TEST2[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #D9D9CD, align: center"]53[/TD]
[TD="bgcolor: #D9D9CD, align: center"]95[/TD]
[TD="bgcolor: #D9D9CD, align: center"]TEST3[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #F0F0F0, align: center"]96[/TD]
[TD="bgcolor: #F0F0F0, align: center"]100[/TD]
[TD="bgcolor: #F0F0F0, align: center"]TEST4[/TD]

</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?

 

[FormR]

You are getting the #VALUE error because you are trying to multiply text values, try:

Excel 2012
	A	B	C	D	E	F	G	H	I	J
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						16	TEST1
Sheet1

Cell Formulas
Range	Formula
J6	=LOOKUP(I6,A2:A6,C2:C6)

 

[Fishboy]

Fantastic! Thanks for the speedy and, more importantly, correct advice!

 

[steve the fish]

If your table is always a sorted table as shown then:

=IF(I6>$B$6,#N/A,LOOKUP(I6,$A$2:$A$6,$C$2:$C$6))