Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting values from a range that equal a specific total | Excel Worksheet Functions | |||
I want to count the highest number of equal cells | Excel Worksheet Functions | |||
looking up figures in a column and selecting next highest match | Excel Worksheet Functions | |||
Selecting the 2nd highest value in a row. | Excel Worksheet Functions | |||
Selecting nearest under in a list | Excel Discussion (Misc queries) |