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

 

[Fluff]

It converts numbers stored as text into real numbers.

 

[etaf]

As this is now solved , I thought i would ask further about the -- to convert text to real numbers
does that work in all cases , just tried on a left() and worked great - usually i use *1
any advantages / disadvantages to using this

I have seen it used for converting True/False to 1 & 0's in functions like sumproduct()

hope you dont mind me side tracking the thread , as it now solved and thought better on the forum for everyone rather than via a PM

 

[Fluff]

As long as all the values can be converted to numbers it should work & I believe it's meant to be faster than using +0 or *1 (although the speed difference is unlikely to be noticeable)

 

[etaf]

thanks for that , i will add to my notes
thsnks agin