Remember Me?

#1
May 26th 08, 07:31 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2008 Posts: 2
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
May 26th 08, 07:45 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 510
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
May 26th 08, 04:29 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,572
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
May 26th 08, 05:11 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 968
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Matt UK Excel Worksheet Functions 4 November 25th 06 09:42 PM fiur Excel Worksheet Functions 6 October 16th 06 04:47 AM [email protected] Excel Worksheet Functions 3 October 4th 05 01:21 AM Tony Excel Worksheet Functions 2 July 27th 05 04:59 PM Purfleet Excel Discussion (Misc queries) 3 April 19th 05 03:19 PM

All times are GMT +1. The time now is 03:50 AM.