Excel Q - Next Highest Value

Factual Questions
1

I’m looking for a formula that will look at a number in a column and choose the next highest number from a predetermined list.

If my spreadsheet is below:



1    2   ?
4    ?   ?
7    8   ?
43   ?   ?
32   ?   ?

And my list of values is:


4
6
7
9
14
34
65
78

Then it would output as:



1    2   4
4    6   7
7    8   9
43  65   78
32  34   65

Thanks!

2

There are some boundary issues but you can do this with a nested combination of vlookup, index and match.

First add a low boundary value that is lower than any value you expect. Zero works in your sample. Next do a vlookup on your range_lookup equal to “true”. Note that this will retrun the value if it is in the list or the next lower value. Next do a match on the lookup list with the results of your vlookup. That gets you the position in range. Finally, an index function against the list with results of the match plus one. If you will lookup the highest value in your list (78 in the sample), add a second row in your lookup range of 78. It will match the first 78 and the plus one will get the next one.

=INDEX(A$1:A$10,MATCH(VLOOKUP(b1,A$1:A$10,1,TRUE),A$1:A$10,0)+1)

In this case, the numbers in A2:A9 are your lookup numbers, A1 is zero and A10 is 78 repeated. We are lookup up a value in b1.

Hopefully someone will post a most simple solution.

Edited to add: My solution always returns 78 if a number higher than 78 is entered.

3

Missed the edit window but you can get rid of the vlookup by using a match-type of 1 on the match function:

=INDEX(A$1:A$10,MATCH(B1,A$1:A$10,1)+1)

4

Thanks!

I did this manually at first but made a number of mistakes. The formula saved the day.