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. |
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. |
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 |
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 |
All times are GMT +1. The time now is 05:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com