look up with from and to ranges

[ExcelNewbie2020]

Sirs,

how to look up a value if the criteria is within the amount range?

based on the table, i need to look up the rate based on "NO." and the "FROM and TO" range. with my example, i'm looking for NO. 3 with an amount of 450000.. since the amount is within the range of 445000 and 477000 so i should have the RATE of 3.1..

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		FROM	TO	FROM	TO	FROM	TO	FROM	TO	FROM	TO	FROM	TO
2	RATE	0	0	1	1	2	2	3	3	4	4	5	5	NO.
3	2.1	334000	363000	365000	394000	393000	420000	417000	445000	444000	470000	472000	496000	AMOUNT
4	3.1	363000	395000	394000	422000	420000	450000	445000	477000	470000	503000	496000	525000
5	4.1	395000	426000	422000	455000	450000	484000	477000	510000	503000	534000	525000	557000
6	5.1	426000	520000	455000	520000	484000	520000	510000	544000	534000	570000	557000	597000
7	6.1	520000	601000	520000	617000	520000	632000	544000	647000	570000	662000	597000	677000
8
9
10			NO.	3	INPUT
11			AMOUNT	450000	INPUT
12			RATE	3.1	FORMULA HERE
Sheet4

 

[James006]

Hi,
For cell D12, you can test following formula:

=INDIRECT("A"&MATCH(D11,INDIRECT(ADDRESS(1,MATCH(D10,$2:$2,0),4)&":"&ADDRESS(7,MATCH(D10,$2:$2,0),4)),1))

 

[shinigamilight]

=MAX(((IF((B1:M1="from")*(B2:M2=D10),B3:M7,0)<=D11)*(IF((C1:N1="to")*(C2:N2=D10),C3:N7,0)>=D11))*A3:A7)

 

[ExcelNewbie2020]

Hi,
For cell D12, you can test following formula:

=INDIRECT("A"&MATCH(D11,INDIRECT(ADDRESS(1,MATCH(D10,$2:$2,0),4)&":"&ADDRESS(7,MATCH(D10,$2:$2,0),4)),1))

result is "RATE"..

 

[ExcelNewbie2020]

=MAX(((IF((B1:M1="from")*(B2:M2=D10),B3:M7,0)<=D11)*(IF((C1:N1="to")*(C2:N2=D10),C3:N7,0)>=D11))*A3:A7)

result shows "0"

 

[Fluff]

How about

=INDEX(A3:A7,XMATCH(D11,CHOOSECOLS(--B3:M7,XMATCH(D10,B2:M2,0)),-1))

 

[James006]

Sorry ...but ... at my end ...formula produces 3.1 ...

 

[ExcelNewbie2020]

How about

=INDEX(A3:A7,XMATCH(D11,CHOOSECOLS(--B3:M7,XMATCH(D10,B2:M2,0)),-1))

thanks man, it works.. do you have quick alternative formula for excel 2019 version?..

 

[Fluff]

How about

=INDEX(A3:A7,MATCH(D11,INDEX(--B3:M7,,MATCH(D10,B2:M2,0)),1))

 

[ExcelNewbie2020]

How about

=INDEX(A3:A7,MATCH(D11,INDEX(--B3:M7,,MATCH(D10,B2:M2,0)),1))

thank you.. could you please elaborate what this 2 negative lines do?