 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Vlookup selecting next highest value instead of equal or nearest l
Hi All . Is there any way we can make Vlookup select the next value that is highest or equal to in a cell instead of the nearest lower value  SignatureJack Hi You can make VLOOKUP select Exact value only setting 4th parameter to False. With 4th paramaeter True (or omitted), your table must be sorted. I myself never use VLOOKUP this way, but probably the sorting order (ascending vs. descending) determines, is nearest higher or lower value returned, when exact match doesn't exist. SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) > Hi All . > Is there any way we can make Vlookup select the next value that is highest > or equal to in a cell instead of the nearest lower value Assuming that the lookup values are sorted ascending, revise this Vlookup() formula: =VLOOKUP(D1,A2:B15,2) with this formula: =VLOOKUP(SMALL(A2:A15,COUNTIF(A2:A15,"<"&$D$1)+1),A2:B15,2) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hi All . > Is there any way we can make Vlookup select the next value that is highest > or equal to in a cell instead of the nearest lower value Hi Jack, another alternative formula (longer but faster) =IF(VLOOKUP(D1,A1:A20,1)=D1,INDEX($A$1:$B$20,MATCH(D1,A1:A20),2),INDEX($A$1:$B$20,MATCH(D1,A1:A20)+1,2)) Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com > Hi All . > Is there any way we can make Vlookup select the next value that is highest > or equal to in a cell instead of the nearest lower value |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.