Data Lookup

CHAS

New Member
Joined
Apr 24, 2002
Messages
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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?
 
Upvote 0
=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
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top