How to retrieve answer from a certain column using figure within a range

[Rhon Alvarez]

Hey guys,

Please help me create an excel formula to give me the result from a certain column if the data amount is within the range given. I could not make use of the index_match formula since given amount is not an exact match and the use of IF function is not possible due to number of formulas needed. I tried rounding off the figure so it would equal the Monthly Salary Credit for easy referencing but to no avail since it would needs rounding up and rounding down.

I have attached the file for reference and thank you in advance.

 

[Chihiro]

Just use SUMIFS.

In J3...
=SUMIFS($E$3:$E$33,$A$3:$A$33,"<="&I3,$B$3:$B$33,">="&I3)

Copy to other cells in J column.

 

[Chihiro]

Or if you are using Excel 2003...
=SUM(IF(($A$3:$A$33<=I3)*($B$3:$B$33>=I3),$E$3:$E$33))

Confirmed as Array (CTRL + SHIFT + ENTER)

Or...
=SUMPRODUCT(($A$3:$A$33<=I3)*($B$3:$B$33>=I3),$E$3:$E$33)

 

[bosco_yip]

Maybe.....

In J3, formula copy down :

=LOOKUP(9^9,E$3:E$33/(A$3:A$33<=I3)/(B$3:B$33>=I3))

Regards
Bosco

 

[Rhon Alvarez]

Hello Guys, (Chihiro and Bosco_Yip)

Once again, thanks for the help. These helps me a lot and I continue to have new and additional learnings from you all.