Data Lookup

CHAS · Apr 25th, 2002, 02:00 PM

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 · Apr 25th, 2002, 02:12 PM

Quote:

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. · Apr 25th, 2002, 02:18 PM

=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 · Apr 25th, 2002, 02:20 PM

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 · Apr 25th, 2002, 02:28 PM

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

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

Aladin

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 02:00 PM	#1
CHAS New Member Join Date: Apr 2002 Posts: 2	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?

Apr 25th, 2002, 02:18 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=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

Apr 25th, 2002, 02:20 PM	#4
CHAS New Member Join Date: Apr 2002 Posts: 2	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