Excel - Reverse Vlookup, List in Descending Order, [range_lookup] = TRUE

Asked By Matt.Russett on 27-Sep-08 01:03 AM
Hello,

I have been trying to find this answer and having a little trouble.

Here is my example:

In Column A there are Dates in Descending order.  In Column B are the
corresponding Fuel Percentages that we charge our customers on those
dates.  An employee from my company will add the new date to the top
of the list if the Fuel Percentage has changed.

I have another workbook that needs to look at every day of the year
and pull the fuel average on that day.  The problem is that when I do
a Vlookup =vlookup(Date,A:B,2,TRUE) the default logic is to look from
the top of the list down to the bottom until it finds the value
closest to the lookup value which is followed by a LARGER value.  It
then returns the value in the 1 row over from the lesser value.  Since
my list is in Descending order (which it needs to be for other
reasons)... it always just takes the value at the very bottom of my
list.

It would be great if there was a Vlookup function that was able to
handle decending arrays or look from the bottom up.  From what I have
read there doesnt seem to be one.

Does anyone have an alternate solution?  I can provide an example
sheet if anyone wants to help

Thank You!




Niek Otten replied on 26-Sep-08 03:44 PM
Hi Matt,

Suppose your table is in A1:B39, and your search date is in C1:

=INDEX(B1:B39,MATCH(C1,A1:A39,-1))

Look in HELP for details
--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Matt.Russett replied on 27-Sep-08 01:03 AM
Here is some sample data


A           B
9/1/08      35%
8/1/08      31%
7/1/08      33%

Lookup Value = 8/15/08
Desired Result = 31%
Formula = =vlookup((8/15/08),A:B,2,TRUE)
Current Result = 33%
Matt.Russett replied on 27-Sep-08 01:03 AM
You Rule!  Thanks!

Someday I will get my brain wrapped around the whole Index Match
thing :)
Niek Otten replied on 26-Sep-08 04:07 PM
Are you sure? Doesn't sound healthy at all!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Matt.Russett replied on 27-Sep-08 01:03 AM
hehe... well I just realized I am still having an issue

1 -- find the largest value less than or equal to lookup_value
(the list must be in ascending order)
0 -- find the first value exactly equal to lookup_value. Lookup_array
(the list can be in any order)
-1 -- find the smallest value greater than or equal to lookup_value.
(the list must be in descending order)

I am using -1

What I need is for it to "find the smallest value LESS THAN or equal
to the lookup

9/24/2008     24.5%
9/17/2008     25.0%
9/10/2008     25.5%

If my lookup value is 9/23/08.... using your prescribed formula it
looks for the value GREATER than or Equal to the Lookup value....  So
it returns 24.5%  when actually the percentage for the week of
9/17/2008-9/23/2008 is 25.0%

And if I use a 1 at the end of the Index/Match formula it does not
work because the list needs to be in Ascending order.

sigh....

any ideas?
RagDyer replied on 26-Sep-08 05:25 PM
With date to find in E1, try this *array* formula:

=VLOOKUP(MAX(IF(A1:A100<=E1,A1:A100)),A1:B100,2,0)

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.  Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Matt.Russett replied on 01-Oct-08 09:57 PM
Ahh that did it, brilliant!  thank you.
RagDyeR replied on 29-Sep-08 09:58 AM
You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ahh that did it, brilliant!  thank you.