PDA

View Full Version : Lookup with interval



slamet Harto
10-27-2010, 12:26 AM
hi there

hope you are doing good.

Please advise how to lookup within an interval value and match the criteria.

I highly appreciate if you can advise me with formula and using vba.

please find the attached for your reference.
Thanks in advance.

mbarron
10-27-2010, 12:02 PM
Change your formula to:

=SUMPRODUCT(--(C3>=LEFT($F$14:$F$31,FIND(" ",$F$14:$F$31)-1)*1)*--(C3<=MID($F$14:$F$31,FIND(" - ",$F$14:$F$31)+ 3,10)*1)*--(D3=$G$14:$G$31)*($H$14:$H$31))

The formula now looks for the " - " to determine where to look for the upper end of the interval.

Your first "should be" is incorrect. The formula was calculating correctly for the combination supplied.

slamet Harto
10-27-2010, 07:29 PM
hi mbarron

It's work. thank you and highly appreciated it.

Have a nice day!