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?

In other words, you want to find the *last* instance of 150 and return the
corresponding value from olumn L?

If that's the case try this:

A1 = 150

=LOOKUP(2,1/(E2:E1011=A1),L2:L1011)

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
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?

Hi,

Assume that your data is set up like this in A1:B6

Item 1 10
Item 2 11
Item 3 12
Item 4 13
Item 1 14
Item 1 15

In A9, enter Item1 and in B9, use the following array formula
(Ctrl+Shift+Enter)

=INDEX(A1:B6,MAX(($A$1:$A$6=A9)*ROW(A1:A6)),2)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sandy" wrote in message
...
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?

my range is E2:L1011.

If the data doesn't start on row 1 then you have to calculate the offset in
order to match the correct *relative* row number of the INDEX function.

A1 = lookup value

Array entered:

=INDEX(L2:L1011,MAX((E2:E1011=A1)*ROW(E2:E1011))-(MIN(ROW(E2:E1011))-1))

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

Assume that your data is set up like this in A1:B6

Item 1 10
Item 2 11
Item 3 12
Item 4 13
Item 1 14
Item 1 15

In A9, enter Item1 and in B9, use the following array formula
(Ctrl+Shift+Enter)

=INDEX(A1:B6,MAX(($A$1:$A$6=A9)*ROW(A1:A6)),2)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sandy" wrote in message
...
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?

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?

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?

Smashing good Dr Valko. Works great. I don't understand how it works or
what the parts are however. If possible could you dumb it down a little and
explain what the mix of functions are? Or if this is a modification of a
function.... or both. I'd like to understand this so I can possibly use this
in other senarios, in particular what makes it search for the "last" matching
entry.

"T. Valko" wrote:

In other words, you want to find the *last* instance of 150 and return the
corresponding value from olumn L?

If that's the case try this:

A1 = 150

=LOOKUP(2,1/(E2:E1011=A1),L2:L1011)

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
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?

Here goes:

Let's use this example to demonstrate how this works:

...........A.............B
1.....header.....header
2........9.............10
3........7.............12
4........9.............15
5........5.............16

Return the value in column B that corresponds to the *last instance* of 9 in
column A.

=LOOKUP(2,1/(A2:A5=9),B2:B5)

This expression will return an array of either TRUE or FALSE:

(A2:A5=9)

A2 = 9 = 9 = TRUE
A3 = 7 = 9 = FALSE
A4 = 9 = 9 = TRUE
A5 = 5 = 9 = FALSE

We then use the divison operation to coerce those logical values to numbers:

A2 = 1 / TRUE = 1
A3 = 1 / FALSE = #DIV/0!
A4 = 1 / TRUE = 1
A5 = 1 / FALSE = #DIV/0!

At this point the formula looks like this:

=LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

The *last* value in the lookup_vector that is less than the lookup_value is
the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/(A2:A5=9)

will not return a value greater than 1.

This is how that would look:

...........A.............B
1.....header.....header
2........1.............10
3...#DIV/0!.......12
4........1.............15
5...#DIV/0!.......16

So, the *last instance* of 9 was in A4. Return the corresponding value from
B4.

=LOOKUP(2,1/(A2:A5=9),B2:B5) = 15


--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
Smashing good Dr Valko. Works great. I don't understand how it works or
what the parts are however. If possible could you dumb it down a little
and
explain what the mix of functions are? Or if this is a modification of a
function.... or both. I'd like to understand this so I can possibly use
this
in other senarios, in particular what makes it search for the "last"
matching
entry.

"T. Valko" wrote:

In other words, you want to find the *last* instance of 150 and return
the
corresponding value from olumn L?

If that's the case try this:

A1 = 150

=LOOKUP(2,1/(E2:E1011=A1),L2:L1011)

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
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?

Fantastic you certainly know your stuff, I have manipulated this formula and
parts of it to work with a few different situations and formulas. Life is a
little easier now, thanks heaps.
I have one other unrelated question if you are willing. I am using a
random number generator. "=Randbetween(1,1000)" this is a volitile function
as it constantly changes every time I enter info into a cell and move on. Is
there a way to get the random number generator to generate only once?

"T. Valko" wrote:

Here goes:

Let's use this example to demonstrate how this works:

...........A.............B
1.....header.....header
2........9.............10
3........7.............12
4........9.............15
5........5.............16

Return the value in column B that corresponds to the *last instance* of 9 in
column A.

=LOOKUP(2,1/(A2:A5=9),B2:B5)

This expression will return an array of either TRUE or FALSE:

(A2:A5=9)

A2 = 9 = 9 = TRUE
A3 = 7 = 9 = FALSE
A4 = 9 = 9 = TRUE
A5 = 5 = 9 = FALSE

We then use the divison operation to coerce those logical values to numbers:

A2 = 1 / TRUE = 1
A3 = 1 / FALSE = #DIV/0!
A4 = 1 / TRUE = 1
A5 = 1 / FALSE = #DIV/0!

At this point the formula looks like this:

=LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

The *last* value in the lookup_vector that is less than the lookup_value is
the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/(A2:A5=9)

will not return a value greater than 1.

This is how that would look:

...........A.............B
1.....header.....header
2........1.............10
3...#DIV/0!.......12
4........1.............15
5...#DIV/0!.......16

So, the *last instance* of 9 was in A4. Return the corresponding value from
B4.

=LOOKUP(2,1/(A2:A5=9),B2:B5) = 15


--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
Smashing good Dr Valko. Works great. I don't understand how it works or
what the parts are however. If possible could you dumb it down a little
and
explain what the mix of functions are? Or if this is a modification of a
function.... or both. I'd like to understand this so I can possibly use
this
in other senarios, in particular what makes it search for the "last"
matching
entry.

"T. Valko" wrote:

In other words, you want to find the *last* instance of 150 and return
the
corresponding value from olumn L?

If that's the case try this:

A1 = 150

=LOOKUP(2,1/(E2:E1011=A1),L2:L1011)

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
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?

See if this helps:

http://mcgimpsey.com/excel/udfs/randint.html

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
Fantastic you certainly know your stuff, I have manipulated this formula
and
parts of it to work with a few different situations and formulas. Life is
a
little easier now, thanks heaps.
I have one other unrelated question if you are willing. I am using a
random number generator. "=Randbetween(1,1000)" this is a volitile
function
as it constantly changes every time I enter info into a cell and move on.
Is
there a way to get the random number generator to generate only once?

"T. Valko" wrote:

Here goes:

Let's use this example to demonstrate how this works:

...........A.............B
1.....header.....header
2........9.............10
3........7.............12
4........9.............15
5........5.............16

Return the value in column B that corresponds to the *last instance* of 9
in
column A.

=LOOKUP(2,1/(A2:A5=9),B2:B5)

This expression will return an array of either TRUE or FALSE:

(A2:A5=9)

A2 = 9 = 9 = TRUE
A3 = 7 = 9 = FALSE
A4 = 9 = 9 = TRUE
A5 = 5 = 9 = FALSE

We then use the divison operation to coerce those logical values to
numbers:

A2 = 1 / TRUE = 1
A3 = 1 / FALSE = #DIV/0!
A4 = 1 / TRUE = 1
A5 = 1 / FALSE = #DIV/0!

At this point the formula looks like this:

=LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

The *last* value in the lookup_vector that is less than the lookup_value
is
the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/(A2:A5=9)

will not return a value greater than 1.

This is how that would look:

...........A.............B
1.....header.....header
2........1.............10
3...#DIV/0!.......12
4........1.............15
5...#DIV/0!.......16

So, the *last instance* of 9 was in A4. Return the corresponding value
from
B4.

=LOOKUP(2,1/(A2:A5=9),B2:B5) = 15


--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
Smashing good Dr Valko. Works great. I don't understand how it works
or
what the parts are however. If possible could you dumb it down a
little
and
explain what the mix of functions are? Or if this is a modification of
a
function.... or both. I'd like to understand this so I can possibly
use
this
in other senarios, in particular what makes it search for the "last"
matching
entry.

"T. Valko" wrote:

In other words, you want to find the *last* instance of 150 and return
the
corresponding value from olumn L?

If that's the case try this:

A1 = 150

=LOOKUP(2,1/(E2:E1011=A1),L2:L1011)

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
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?