multi-layer IF then help

[dbwiz]

I have a schedule of amounts in Column A12 to B21(basically a "From" and "To" range), and a BP amount in C12:C21. The number in E8 needs to be compared to see if it is >=A12<=B12 and if True, multiply the number in E8*C12*.001 and show that amount of compensation in E10. If false, it needs to be compared to see if it falls between A13 and B13, etc. etc.

Sheet1[TABLE="class: html-maker-worksheet"]
<THEAD>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH]12[/TH]
[TD="align: center"]$0[/TD]
[TD="align: center"]$2,500,000[/TD]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD="align: center"]$2,500,001[/TD]
[TD="align: center"]$5,000,000[/TD]
[TD="align: center"]65[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD="align: center"]$5,000,001[/TD]
[TD="align: center"]$7,500,000[/TD]
[TD="align: center"]55[/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD="align: center"]$7,500,001[/TD]
[TD="align: center"]$10,000,000[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD="align: center"]$10,000,001[/TD]
[TD="align: center"]$15,000,000[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD="align: center"]$15,000,001[/TD]
[TD="align: center"]$20,000,000[/TD]
[TD="align: center"]35[/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD="align: center"]$20,000,000[/TD]
[TD="align: center"]$25,000,000[/TD]
[TD="align: center"]32[/TD]
[/TR]
[TR]
[TH]19[/TH]
[TD="align: center"]$25,000,001[/TD]
[TD="align: center"]$30,000,000[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TH]20[/TH]
[TD="align: center"]$30,000,001[/TD]
[TD="align: center"]$35,000,000[/TD]
[TD="align: center"]27.5[/TD]
[/TR]
[TR]
[TH]21[/TH]
[TD="align: center"]$35,000,001[/TD]
[TD="align: center"]$40,000,000[/TD]
[TD="align: center"]25[/TD]
[/TR]
</TBODY>[/TABLE]
Excel 2007



I can get the first if no problem but always get an error trying to add more scenarios. Can someone help?

 

[pgc01]

Hi

If I understood correctly, in E10

=LOOKUP(E8,$A$12:$A$21,$C$12:$C$21)*E8*0.001

 

[Deep Dave]

=e8*offset(a11,match(vlookup(e8,$a$12:$a$21,1,true),$a$12:$a$21,0),2)*0.001

 

[Banzai Otis]

Another solution, using an array formula

{= SUM(($E$8 >= $A$12:$A$21) * ($E$8 <= $B$12:$B$21) * $C$12:$C$21) * $E$8 * 0.001}

Remember to type it without brackets and hit ctl+shift+enter when you are done.

 

[dbwiz]

Thank you all. What if my lookup was on a different spreadsheet? I have a similar one where B10 needs to lookup the same columns A1:A10,C1:C10 on a spreadsheet file called CSU and multiply B10 by the result*.0001.

 

[Banzai Otis]

It sounds like you just need to change the cell references in my formula. If you want to go through this same process with a value in a different cell, then replace $E$8 with that different cell. If you want your lookup table in a different sheet then the formula references will need to include that. For example, if your lookup table is in a sheet name TableSheet, and you want to do this calculation with a value from cell B10 in your current sheet, it would look like this:

{= SUM(($B$10 >= 'TableSheet'!$A$12:$A$21) * ($B$10 <= 'TableSheet'!$B$12:$B$21) * 'TableSheet'!$C$12:$C$21) * $B$10 * 0.001}

Does that answer the question?

Jason