Thanks:  0
Likes:  0

1. 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?

2. 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?

3. =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

4. 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

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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•