Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup must return a value that is greater than
If a value is not found in a range, I would like it to return the smallest
value greater than itself. A B C i.e. 8 2 10 4 12 6 By default if I searched for 9, and needed the info from column C, I would 2 as my result, but instead I would like to get 4 as the result. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup must return a value that is greater than
Sort your lookup data on col A descending
=INDEX(C1:C3,MATCH(9,A1:A3,-1)) HTH -- AP "BG" a écrit dans le message de news: ... If a value is not found in a range, I would like it to return the smallest value greater than itself. A B C i.e. 8 2 10 4 12 6 By default if I searched for 9, and needed the info from column C, I would 2 as my result, but instead I would like to get 4 as the result. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup must return a value that is greater than
If you use MATCH( ) then you will get the (relative) position in the
range - you can add 1 onto this and feed this into an INDEX( ) function to get what you want. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup must return a value that is greater than
What if that relative is an exact match or the last one?
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "Pete_UK" wrote in message oups.com... If you use MATCH( ) then you will get the (relative) position in the range - you can add 1 onto this and feed this into an INDEX( ) function to get what you want. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I return a blank for VLOOKUP instead of #N/A? | Excel Worksheet Functions | |||
Excel 2000 VLOOKUP returns #N/A unless press F2 return on source? | Excel Worksheet Functions | |||
vlookup - finding the next value that is GREATER than the lookup value? | Excel Worksheet Functions | |||
Want VLookup to Return the row above | Excel Worksheet Functions | |||
Vlookup of an if statement return | Excel Worksheet Functions |