Excel: Getting the next value in a table rather than the first match

I recently needed to get the next biggest value in a table of values, and after surfing the net and looking at a few examples that where a little confusing this is how it is done:
  • Use VLookup(value, TableRange, 1, TRUE) - true means approximate value, which means that in a sorted table it will choose the value equal or less than
  • Then using Match(theVLookupAbove, TableRange) - This will return the row number of the value
  • Then take the result from that use Index( TableRange, theMatchAbove + 1)
This will give you the result you are looking for.

Example:

2^NVALUE100
1
2 FormulaValueFormula in Text
4 VLOOKUP64=VLOOKUP(D1,A2:A10,1,TRUE)
8 MATCH7=MATCH(D4,A2:A10)
16 INDEX128=INDEX(A2:A10,D5+1)
32 AllInOne128=INDEX(A2:A10,MATCH(VLOOKUP(D1,A2:A10,1,TRUE),A2:A10)+1)
64
128
256

Comments