Data Lookup

[CHAS]

I have used the VLOOKUP command but cannot get my desired output. When I type in the "input value", the result is the largest value in my lookup table that is smaller that the "input value". This is how it should work. But, I need for the result to return the next largest value in the lookup table. Any suggestions?

 

[Aladin Akyurek]

On 2002-04-25 13:00, CHAS wrote:
I have used the VLOOKUP command but cannot get my desired output. When I type in the "input value", the result is the largest value in my lookup table that is smaller that the "input value". This is how it should work. But, I need for the result to return the next largest value in the lookup table. Any suggestions?

Care to provide a small example along with expected results?

 

[Mark W.]

=OFFSET(INDEX(A1:A6,MATCH(B1,A1:A6)),1,)

where B1 contains your lookup value. If B1 is 2 and A1:A6 contains {1;2;3;4;5;6} or {1;1.9;3;4;5;6} then this formula returns 3

 

[CHAS]

If my "input value" is 21.5, then I want the return to 25. Below is my table.


1
3
6
10
15
20
25
30
35
40
45
50
60
70
80
90
100
110
125
150
175
200
225
250
300
350
400
450
500
600
601
700
800
1000
1200
1600
2000
2500
3000
4000
5000

 

[Aladin Akyurek]

=INDEX(A2:A100,MATCH(E1,A2:A100)+1)

where A2:A100 houses the data (adjust to suit) and E1 an "input value".

Aladin

On 2002-04-25 13:20, CHAS wrote:
If my "input value" is 21.5, then I want the return to 25. Below is my table.


1
3
6
10
15
20
25
30
35
40
45
50
60
70
80
90
100
110
125
150
175
200
225
250
300
350
400
450
500
600
601
700
800
1000
1200
1600
2000
2500
3000
4000
5000