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 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^N VALUE 100 1 2 Formula Value Formula in Text 4 VLOOKUP 64 =VLOOKUP(D1,A2:A10,1,TRUE) 8 MATCH 7 =MATCH(D4,A2:A10) 16 INDEX 128 =INDEX(A2:A10,D5+1) 32 AllInOne 128 =INDEX(A2:A10,MATCH(VLOOKUP(D1,A2:A10,1,TRUE),A2:A10)+1) 64 128 256