% range lookup

[Peter1973]

I am trying to get a lookup to look at a % in cell D1 then find the % value in A1:A100 that is closest to that value but not higher than it and return the value from that corresponding cell from B1:B100. Like below it would return £5.50.

Any help appreciated

 

[etaf]

=index(B4:B15,match(d2,A4:A15,1))

Book1
	A	B	C	D	E	F	G
1
2				72.50%			£ 5.50
3
4	45%	£ 3.00
5	50%	£ 3.50
6	55%	£ 4.00
7	60%	£ 4.50
8	65%	£ 5.00
9	70%	£ 5.50
10	75%	£ 6.00
11	80%	£ 6.50
12	85%	£ 7.00
13	90%	£ 7.50
14	95%	£ 8.00
15	100%	£ 8.50
16
Sheet2

Cell Formulas
Range	Formula
G2	G2	=INDEX(B4:B15,MATCH(D2,A4:A15,1))

 

[Alex Blakenburg]

If your list is in order like you are showing then a VLookup with the True parameter will do that for you.

Book1
	A	B	C	D	E
1				Lookup	Return
2				72.50%	$ 5.50
3	Percent	Value
4	45%	$ 3.00
5	50%	$ 3.50
6	55%	$ 4.00
7	60%	$ 4.50
8	65%	$ 5.00
9	70%	$ 5.50
10	75%	$ 6.00
11	80%	$ 6.50
12	85%	$ 7.00
13	90%	$ 7.50
14	95%	$ 8.00
15	100%	$ 8.50
Sheet1

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP($D$2,$A$4:$B$15,2,TRUE)
A5:A15	A5	=A4+0.05
B5:B15	B5	=B4+0.5

 

[Peter1973]

Thank you

 

[etaf]

you are welcome