I am using VLOOKUP with the not_exact_match set to True, however instead of finding the next largest value that is less than value, I want to find a way of returning the next largest value that is greater than value. I have looked at using MATCH and OFFSET to try and increment the returned value by 1, but so far haven't managed to figure this out.
Can anyone suggest a way to achieve this.
Many thanks
Perhaps apply LARGE
=SMALL(range,1+COUNTIF(range,"<="&value))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks DonkeyOte. I don't think this will work. It is conditional depending on whether VLOOKUP does or doesn't find an exact match.
If it does find an exact match then it uses the value obtained by the VLOOKUP, however if it doesn't find an exact match I simply want a formula to get the value in the cell below, but it still needs to incorporate a VLOOKUP.
If a value does exist can it appear more than once ?
If not a basic:
=INDEX(<return range>,MATCH(<critera>,<criteria range>)+(LOOKUP(<criteria>,<criteria range>)<><criteria>))
(assuming data is sorted - implied in first post)
If in doubt - post a sample - covers half a dozen posts to & fro.
From the Help for VLOOKUP:
If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
Important: If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
From the Help for Match:
1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
So, you can use INDEX/MATCH but your data must be sorted in descending order.
=INDEX(A:A,MATCH(C1,A:A,-1))
Regards
Thanks DonkeyOte and TMShucks for your replies.
I think I may need to make my problem a bit cleaerer. I have attached a simple example to hopefully demonstrate what I want to achieve.
The givens are that the data is (and has to remain) sorted in ascending order by date (Column A).
I have a named ranged called Table in A2:B21. In Column D, I have specified some dates to lookup in my named range and in Column F, I have detailed the results of the lookup, using the formula =VLOOKUP(Cell Ref,Table,2,TRUE). The VLOOKUP is looking for the date from Column D within my named range and returning the value for Interest Accrued from Column B.
When an exact match is found, I get the correct result, however when an exact match isn't found, I get the next largest value that is less than value (as expected), so when it is looking for 31-Jan-09 it returns the value for 31-Dec-08.
What I want though is a way of finding the next largest value that is greater than value. So when looking for 31-Jan-09 instead of returning the value for 31-Dec-09, I want it to return the value for 31-Mar-09. The same applies when looking for 28-Feb-09, again I want a formula that will return 31-Mar-09, i.e. the next largest value that is greater than value, rather than the next largest value that is less than value.
This is where I am stuck.
Last edited by HangMan; 09-13-2010 at 03:35 AM.
Per the sample and use of Named Range
this is pretty much the same as my prior post just with range references filled in as appropriate
Please Login or Register to view this content.
And adapting the COUNTIF concept:
though shorter the above is less efficient than the first examplePlease Login or Register to view this content.
Last edited by DonkeyOte; 09-13-2010 at 04:11 AM.
Hey DonkeyOte
Many thanks, both these solutions worked perfectly. Very much appreciated.
Out of interest an just so I understand, what makes the second approach less efficient?
The first approach uses two Binary Search based functions and these are very fast. Binary Searches use only a very small subset of the precedent range.
(for a graphical representation of the basics see: http://www.mrexcel.com/forum/showpos...98&postcount=7)
The second approach though shorter in terms of syntax uses a COUNTIF statement - this means each cell in the precedent range (within the intersect of the used range) is processed.
In truth, given COUNTIF is itself pretty efficient, the difference in performance will be negligible on anything but immense data sets and/or where COUNTIF used in large volume.
Sometimes for legibility or complicated calculations you do not what to include the search array more than once. Here is a trick to fool vlookup or hlookup to give you the next highest instead of next lowest number in a sorted list.
for example hlookup(2.5,{1,2,3,4},1) = 2, but you wanted 3
so you negate the result, value, array, and resort to get this result:
-hlookup(-2.5,{-4,-3,-2,-1},1) = 3
@DonkeyOte
Excellent sir!!!! Just brilliant!! It helped me a lot. I just registered to this site only for giving you a big Thank you.
regards
Rabiul Alam
Writing from Bangladesh.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks