Skip to main content

Posts

Showing posts from January, 2012

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 thanThen using Match(theVLookupAbove, TableRange) - This will return the row number of the valueThen take the result from that use Index( TableRange, theMatchAbove + 1)This will give you the result you are looking for.
Example:
2^NVALUE10012FormulaValueFormula in Text4VLOOKUP64=VLOOKUP(D1,A2:A10,1,TRUE)8MATCH7=MATCH(D4,A2:A10)16INDEX128=INDEX(A2:A10,D5+1)32AllInOne128=INDEX(A2:A10,MATCH(VLOOKUP(D1,A2:A10,1,TRUE),A2:A10)+1)64128256