How to use LOOKUP, bottom-up with intervals

[rosersol]

Hi guys!

I need help regarding LOOKUP formula!

I have a % commision Matrix. The rows & columns are an intervals data. Rows intervals data are organizated bottom-up (bottom the lower figure, up the higher figer).

How I can get the the % of commision that one saler will get acording his results?

Thank you in advance for your help!

[TABLE="width: 477"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5, align: center"]CARS</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]GROWTH</SPAN>[/TD]
[TD="align: center"]Minimum</SPAN>[/TD]
[TD="align: center"]5,00%</SPAN>[/TD]
[TD="align: center"]10,01%</SPAN>[/TD]
[TD="align: center"]15,01%</SPAN>[/TD]
[TD="align: center"]20,01%</SPAN>[/TD]
[TD="align: center"]25,01%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Maximum</SPAN>[/TD]
[TD="align: center"]10,00%</SPAN>[/TD]
[TD="align: center"]15,00%</SPAN>[/TD]
[TD="align: center"]20,00%</SPAN>[/TD]
[TD="align: center"]25,00%</SPAN>[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]TRUCKS</SPAN>[/TD]
[TD="align: center"]40,01%</SPAN>[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]55%</SPAN>[/TD]
[TD="align: center"]90%</SPAN>[/TD]
[TD="align: center"]120%</SPAN>[/TD]
[TD="align: center"]140%</SPAN>[/TD]
[TD="align: center"]160%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]35,01%</SPAN>[/TD]
[TD="align: center"]40,00%</SPAN>[/TD]
[TD="align: center"]45%</SPAN>[/TD]
[TD="align: center"]75%</SPAN>[/TD]
[TD="align: center"]110%</SPAN>[/TD]
[TD="align: center"]130%</SPAN>[/TD]
[TD="align: center"]150%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]30,01%</SPAN>[/TD]
[TD="align: center"]35,00%</SPAN>[/TD]
[TD="align: center"]35%</SPAN>[/TD]
[TD="align: center"]65%</SPAN>[/TD]
[TD="align: center"]100%</SPAN>[/TD]
[TD="align: center"]120%</SPAN>[/TD]
[TD="align: center"]140%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]25,01%</SPAN>[/TD]
[TD="align: center"]30,00%</SPAN>[/TD]
[TD="align: center"]30%</SPAN>[/TD]
[TD="align: center"]45%</SPAN>[/TD]
[TD="align: center"]80%</SPAN>[/TD]
[TD="align: center"]85%</SPAN>[/TD]
[TD="align: center"]90%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]20,00%</SPAN>[/TD]
[TD="align: center"]25,00%</SPAN>[/TD]
[TD="align: center"]25%</SPAN>[/TD]
[TD="align: center"]30%</SPAN>[/TD]
[TD="align: center"]60%</SPAN>[/TD]
[TD="align: center"]65%</SPAN>[/TD]
[TD="align: center"]70%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Minimum</SPAN>[/TD]
[TD="align: center"]Maximum</SPAN>[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="colspan: 3, align: center"]REAL VALUES</SPAN>[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12,50%</SPAN>[/TD]
[TD="colspan: 2, align: center"]% CARS [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]32,00%</SPAN>[/TD]
[TD="colspan: 2, align: center"]% TRUCKS [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]65,00%</SPAN>[/TD]
[TD="colspan: 2, align: center"]HOW I GET IT?</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[billszysz]

Hi

Your data from A1 (i mean "GROWTH" in A1 and so on). Real values -> for cars in D10, for Trukcs in D11 So result in D12 is

=IF(OR($D$10<0.05,$D$11<0.2),0,INDEX($C$3:$G$7,MATCH(IF($D$11>$B$3,$B$3,$D$11),$B$3:$B$7,-1),MATCH($D$10,$C$1:$G$1)))

Regards

P.S. below is link to the file with this solution (my google drive)

https://drive.google.com/file/d/0B6UlMk8OzUrxaFdPSWU0aUhweFU/view?usp=sharing