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!

			CARS</SPAN>
	GROWTH</SPAN>	Minimum</SPAN>	5,00%</SPAN>	10,01%</SPAN>	15,01%</SPAN>	20,01%</SPAN>	25,01%</SPAN>
	Maximum</SPAN>	10,00%</SPAN>	15,00%</SPAN>	20,00%</SPAN>	25,00%</SPAN>
TRUCKS</SPAN>	40,01%</SPAN>		55%</SPAN>	90%</SPAN>	120%</SPAN>	140%</SPAN>	160%</SPAN>
35,01%</SPAN>	40,00%</SPAN>	45%</SPAN>	75%</SPAN>	110%</SPAN>	130%</SPAN>	150%</SPAN>
30,01%</SPAN>	35,00%</SPAN>	35%</SPAN>	65%</SPAN>	100%</SPAN>	120%</SPAN>	140%</SPAN>
25,01%</SPAN>	30,00%</SPAN>	30%</SPAN>	45%</SPAN>	80%</SPAN>	85%</SPAN>	90%</SPAN>
20,00%</SPAN>	25,00%</SPAN>	25%</SPAN>	30%</SPAN>	60%</SPAN>	65%</SPAN>	70%</SPAN>
	Minimum</SPAN>	Maximum</SPAN>
			REAL VALUES</SPAN>
			12,50%</SPAN>	% CARS
			32,00%</SPAN>	% TRUCKS
			65,00%</SPAN>	HOW I GET IT?</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[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