Vlookup 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 -- Jack |
Vlookup selecting next highest value instead of equal or nearest l
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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Jack" wrote in message ... 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 -- Jack |
Vlookup selecting next highest value instead of equal or nearest l
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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jack" wrote in message ... 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 -- Jack |
Vlookup selecting next highest value instead of equal or nearest l
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 "Jack" wrote in message ... 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 -- Jack |
All times are GMT +1. The time now is 08:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com