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?