Lookup for 2 different values and do the calculation

[SUHAS KARHADKAR]

Respected Excel Ninjas,

Greetings and Happy New Year 2017.

I've attached the file (File Name - Test.xlsx) and described the problem in that file. I want to look up for two values and do the calculation accordingly. Need excel formula for this.

Thanking you in advance.

Regards.

 

[bosco_yip]

Try,

1] Select Cells J7:R31 >> click Format Painter >> paste to lower unused cells J34:R58

2] Un-merge all cells in J7:R31

3] In J7, formula copy across to P7 and all copy down :

=IF(LOOKUP($E7,{0,1.26,2.51,5.01,7.51,10.01,12.51},$J$6:$P$6)=J$6,J$6*$I7,0)

4] In Q7, formula copy down :

=SUM(J7: P7)

5] In R7, formula copy down :

=Q7*$R$6

6] Select Cells J34:R58 >> click Format Painter >> paste back to cells J7:R31

7] Delete Cells J34:R58 format

Regards
Bosco

 

[SUHAS KARHADKAR]

Thanx & Thanx & Thanx & Thanx Bosco. You solved my big problem. You people are the Master of Masters. I am the user only.

Happy New Year and once again a Big Thank you.

Best Regards.

Suhas