thank you Ron,
is there a way to look up the largest number in that list,
that would actually be more effective for what i am trying to do
thanks for your time and effort,
barry

also, im trying to a range in one column based on a if the date in another
is any of the last 7 days, i want to repeat this about 1000 times, is there
an easier way to do this then with a nested sumif or a dsum formula?


"Ron Coderre" wrote:

There's an error in your explanation:

With
A1: 2
A2: 500
A3: 10
A4: text

This formula: =LOOKUP(1E100,A2:A100)
returns 10....not 500

The way that LOOKUP works is:
.. It assumes the numbers in the list are in ascending order.
.. It searches through the list for the lookup value (ignoring text).
.. If the lookup value is larger than any item in the list,
it returns the last item in the list.

Note: This is a version that returns the
last text value (ignoring numbers):
=LOOKUP(REPT("z",255),A:A)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Fred Smith" wrote in message
...
The formula tells Excel to find the number 1e100 in the range A2:A100.
1e100 is a very large number (10 to the 100th power). As there is no
number that large in the range, Excel returns the largest number it finds.

Regards,
Fred.

"BROCK8292" wrote in message
...
thats incredible, thank you very much
im sure youre busy but if you get a chance
i would love to know why that works
br

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,A2:A100)

Format as DATE


--
Biff
Microsoft Excel MVP


"BROCK8292" wrote in message
...
Im trying to select the last date in each of about 1000 seperate 50
row
lists
in a worksheet using a single cell at the top. Is there a formula that
can
do
this?
thank you,
barry