vlookup with true range lookup

[scorpidxls]

hi, could you all help me with this case. I have a multiple prod code and a tiering interest rate table, and I would like to lookup to that table

cd	amount	rate
383	-	0.00%
383	100,000,000	1.50%
383	500,000,000	2.50%
422	-	0.00%
422	50,000,000	0.05%
422	100,000,000	0.10%
422	500,000,000	0.20%
422	1,000,000,000	0.30%
425	-	0.00%
425	500,000,000	0.50%
425	10,000,000,000	1.00%
425	25,000,000,000	3.00%

for an example, say that I want to find the rate for this :

cd	422
amount	499,000,000
rate	???

what function should I use, is it just using vlookup with true range lookup? please help. Thanks in advance

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	cd	amount	rate
2	383	0	0.00%		cd	422
3	383	100000000	1.50%		amount	499000000
4	383	500000000	2.50%		rate	0.10%
5	422	0	0.00%
6	422	50000000	0.05%
7	422	100000000	0.10%
8	422	500000000	0.20%
9	422	1000000000	0.30%
10	425	0	0.00%
11	425	500000000	0.50%
12	425	10000000000	1.00%
13	425	25000000000	3.00%
14
Sheet6

Cell Formulas
Range	Formula
F4	F4	=TAKE(FILTER(C2:C100,(A2:A100=F2)*(B2:B100<=F3),""),-1)

 

[DanteAmor]

Try:

Libro1
	A	B	C	D	E	F
1	cd	amount	rate		cd	422
2	383	0	0.00%		amount	499,000,000
3	383	100,000,000	1.50%		rate	0.10%
4	383	500,000,000	2.50%
5	422	0	0.00%
6	422	50,000,000	0.05%
7	422	100,000,000	0.10%
8	422	500,000,000	0.20%
9	422	1,000,000,000	0.30%
10	425	0	0.00%
11	425	500,000,000	0.50%
12	425	10,000,000,000	1.00%
13	425	25,000,000,000	3.00%
Hoja1

Cell Formulas
Range	Formula
F3	F3	=LOOKUP(2,1/((A2:A13=F1)*(B2:B13<=F2)),C2:C13)

 

[scorpidxls]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	cd	amount	rate
2	383	0	0.00%		cd	422
3	383	100000000	1.50%		amount	499000000
4	383	500000000	2.50%		rate	0.10%
5	422	0	0.00%
6	422	50000000	0.05%
7	422	100000000	0.10%
8	422	500000000	0.20%
9	422	1000000000	0.30%
10	425	0	0.00%
11	425	500000000	0.50%
12	425	10000000000	1.00%
13	425	25000000000	3.00%
14
Sheet6

Cell Formulas
Range	Formula
F4	F4	=TAKE(FILTER(C2:C100,(A2:A100=F2)*(B2:B100<=F3),""),-1)

Thanks alot sir...it works really well! thanks for the help

 

[Fluff]

Glad we could help & thanks for the feedback.