Vlookup with 9E+307

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Well that worked, as your answers always do, but can you explain why? What
does the "^" do?
 
^ is XL's symbol for exponentiation, i.e., X^Y is "X raised to the Yth
power," etc.

Dave
 
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
 
So why does this work and 9E+307 doesn't?
VLOOKUP(LOOKUP(9E+307,B:B),B:D,3,FALSE)
Click to expand...

It works for me but you're doing a double lookup when you only need to do a
single lookup. That's the logic people use on me when I post this:

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

Biff
 
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?
 
Ah! You're right! I didn't think of skipping the second lookup, but I don't
need it, do I? You're so brilliant -- thanks!
 
You're welcome. Thanks for the feedback!

Biff

Patti said:
Ah! You're right! I didn't think of skipping the second lookup, but I
don't
need it, do I? You're so brilliant -- thanks!
Click to expand...
 
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.
 
Back
Top