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

[Matt.Russett]

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]

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



| 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!

 

[Matt.Russett]

Here's 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]

You Rule! Thanks!

Someday I'll get my brain wrapped around the whole Index Match
thing

 

[Niek Otten]

<I'll get my brain wrapped>

Are you sure? Doesn't sound healthy at all!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| You Rule! Thanks!
|
| Someday I'll get my brain wrapped around the whole Index Match
| thing

 

[Matt.Russett]

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]

With date to find in E1, try this *array* formula:

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

 

[Matt.Russett]

Ahh that did it, brilliant! thank you.

 

[RagDyeR]

You're welcome, and appreciate the feed-back.