Biff used a slightly longer version of the formula because,
well, I don't know why, but it really doesn't matter because his
method really does exactly what mine does, or mine does
what his does.
=LOOKUP(2,-(A1:A6=E1),B1:B6)

That will *always* return the value from B6.

Try it with this data:

...........A..........B..........E
1........x...........5..........x
2........y...........4
3........x...........1
4........x...........2
5........v...........4
6........k...........7

Where (A1:A6=E1) = FALSE

Then: -FALSE = 0

While (A1:A6=E1) = FALSE

Then: 1/FALSE = #DIV/0! which LOOKUP ignores.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
Hi Sandy,

If Biff's (Valko) solution is obscure, consider this:

=LOOKUP(2,-(A1:A6=E1),B1:B6)

We are looking up 2 in the list of results returned by the -(A1:A6=E1)
(I'm
using my own cell addresses) A1:A6=E1 returns a set of TRUE's and
FALSE's.
by taking the negative you force Excel to convert the TRUE's to 1's and
the
FALSE's to 0. So this portion of the formula becomes something of the
form
{1,0,1,0,0,1}
Next you ask Excel to find 2 in that list, too bad there is no 2. If the
number LOOKUP is looking for is bigger than any of the numbers in the list
it
picks the last occurance of the largest number it finds. 1 is the largest
number the last 1 is in the 6th positions. Excel then looks at the range
B1:B6 and returns the 6th item.

Now if you understand this the first time through, congradulations, I must
have explained it well. Biff used a slightly longer version of the
formula
because,... well, I don't know why, but it really doesn't matter because
his
method really does exactly what mine does, or mine does what his does.

There is a problem with these types of formulas, they are very obscure.
But
I am no less likely to use them because of this, so I have no room to
talk.

--
Thanks,
Shane Devenshire


"Sandy" wrote:

I'm using 2007 exel and the vlookup function seems to be what I
need....Almost. I have a growing list of numbers, curently with over 1000
rows. I want to lookup a number in column E and get the corresponding
number
in column L, my range is E2:L1011. When I set up a vlookup it finds the
number 150 no problem however it starts looking at the top of the range
at
E2. I want the function to start at E1011 and work its way up so I get
the
most recent entry. Any Ideas?