Complex formula with IF & VLOOKUP functions..help

[rfreeman]

Hello I need help in creating a formula that looks up a tariff and cross references it with 1 of 3 usage scenarios to return a monthly fee.

As per the attached the user would enter a usage value in B1, and select the Tarriff in B2.
Dependign on their data entry the formula needs to lookup the usage/tarrif in B1&B2 and cross reference the table in E1:I22, and return the applicable monthly fee for the usage and tarriff.

I have tried several IF functions and cannot get it to work, can someone please explain the best method?

 

[Somendra Misra]

Hi,

Try in B3:
=INDEX($I$2:$I$22,SUMPRODUCT(MATCH(1,($B$2=$F$2:$F$22)*($B$1>=$G$2:$G$22)*($B$1<=$H$2:$H$22),0)))

Regards,

 

[rfreeman]

As a follow on I have written the following formula, however this is quite long and only looks up 1 of the 7 tarriffs.

=IF(AND($B$3="NSP56",$B$1>=0,$B$1<=2000),S3,IF(AND($B$1>=2001,$B$1<=5000),S4,IF(AND($B$1>=5001,$B$1<=9999999999),S5,"")))

 

[rfreeman]

Thanks for the response.
One of my collegues actually just responded and supplied a very easy formula using the SUMIF formula that i thought i should share.

This works really well
=SUMIFS(I3:I23,F3:F23,B2,G3:G23,"<"&B1,H3:H23,">="&B1)