They are the same thing only in that they are both huge numbers.

9E+307 is not same as 9.9E+306 when you move the decimal +nnn places then
the most significant digits are not the same. Now as to why 99^99 worked and
9E+307 didn't, that's a curiosity.

Biff - you'd find me in your corner on this issue. In point of fact, if 101
would do the trick, it is actually what you should use. By using a more
specific value such as that, you are implicitly setting a limit on what the
compared value can be. It is even more important when you have a specified
limit. If it is a case of "any large number will do, don't care" - then I
also don't care. But coming from a time when memory and CPU cycles were
scarce, I really don't like to knowingly use more of either than I have to.
"Patti" wrote:

Thanks Biff. That helps me understand the 99^99, but I still don't
understand why this worked and not the 9E+307 -- aren't they the same thing?

"Biff" wrote:

You're looking for the last date in a range. A date is just a number that's
formatted to look like a date. The way that these formulas work is that if
the lookup value is a number that is greater than the max value in the range
the result will be to return the last numeric value in the range that is
less than the lookup value. So, we use an arbitrary number that is
guaranteed to be larger than the max value in the range. It seems that the
"standard" for this arbitrary value is 9.99999999999999E+307. Personally, I
think that value confuses a lot of people. How many 9's do I have to
type?????? WTF!

So, in the formulas I posted I just used a different arbitrary large number:
99^99. 99 to the 99th power or 3.69729637649726E+197

Basically, all you need is a lookup value that is 1 greater than the max
value in the range. You could also use something like this:

=VLOOKUP(MAX(B1:B10)+1,B1:D10,3)

When I post something like that I always seem to get "ganged up" on! <VVBG

But to me it makes sense. If you know the max possible value will NEVER be
greater than, say, 100, why use a lookup value of 9.99999999999999E+307 ?
All you need to use is 101.

Biff

"Patti" wrote in message
...
Well that worked, as your answers always do, but can you explain why?
What
does the "^" do?

"Biff" wrote:

Try this:

=VLOOKUP(99^99,B1:D10,3)

Or:

=LOOKUP(99^99,B1:B10,D1:D10)

Biff

"Patti" wrote in message
...
Having an issue with a formula...

VLOOKUP(LOOKUP(9E+307,B:B),B:D,3,FALSE)

I want to find the last date in column B and then pull the date from
column
D in the same row.

This works fine as long as there's no data in column C, but if there
is,
the
formula shows 0 instead of the data in column D.

Any idea why this is happening and how to fix it?

Thanks!