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!

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!

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!

^ is XL's symbol for exponentiation, i.e., X^Y is "X raised to the Yth
power," etc.

Dave
--
Brevity is the soul of wit.


"Patti" wrote:

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!

Hi Dave,

Thanks. So why does this work and 9E+307 doesn't?

"Dave F" wrote:

^ is XL's symbol for exponentiation, i.e., X^Y is "X raised to the Yth
power," etc.

Dave
--
Brevity is the soul of wit.


"Patti" wrote:

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!

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!

So why does this work and 9E+307 doesn't?
VLOOKUP(LOOKUP(9E+307,B:B),B:D,3,FALSE)

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

"Patti" wrote in message
...
Hi Dave,

Thanks. So why does this work and 9E+307 doesn't?

"Dave F" wrote:

^ is XL's symbol for exponentiation, i.e., X^Y is "X raised to the Yth
power," etc.

Dave
--
Brevity is the soul of wit.


"Patti" wrote:

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!

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!

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!

"Biff" wrote:

So why does this work and 9E+307 doesn't?
VLOOKUP(LOOKUP(9E+307,B:B),B:D,3,FALSE)

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

"Patti" wrote in message
...
Hi Dave,

Thanks. So why does this work and 9E+307 doesn't?

"Dave F" wrote:

^ is XL's symbol for exponentiation, i.e., X^Y is "X raised to the Yth
power," etc.

Dave
--
Brevity is the soul of wit.


"Patti" wrote:

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!

You're welcome. Thanks for the feedback!

Biff

"Patti" wrote in message
...
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!

"Biff" wrote:

So why does this work and 9E+307 doesn't?
VLOOKUP(LOOKUP(9E+307,B:B),B:D,3,FALSE)

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

"Patti" wrote in message
...
Hi Dave,

Thanks. So why does this work and 9E+307 doesn't?

"Dave F" wrote:

^ is XL's symbol for exponentiation, i.e., X^Y is "X raised to the Yth
power," etc.

Dave
--
Brevity is the soul of wit.


"Patti" wrote:

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!

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!