99^99 is also a very large number so that's why you will see it used as
well.


Gord

On Tue, 7 Apr 2009 18:10:39 -0400, "T. Valko"
wrote:

=LOOKUP(1E100,A1:D1)

..........A..........B..........C..........D
1....................10.........7.............

Result = 7

How it works:

If the lookup value is greater than *every* value in the referenced range
the formula will return the *last* value from the range that is *less than*
the lookup value.

In the above the lookup value is 1E100 which is scientific notation for a
very large number, 1 followed by 100 0s. There is a very good chance that no
number in the range will be greater than 1E100. So, this meets the condition
that the lookup value is greater than any value in the referenced range so
the formula returns the *last* value in the range that is less than 1E100
which is the rightmost value in the range, 7.

When someone posts this type of question in the forum we (almost always)
don't know how big the numbers will be in the referenced range so we use an
arbitrary number for the lookup value that is guaranteed to be greater than
every value in the range. 1E100 is the arbitrary number that I use.

Technically, you just need a number greater than the max number in the
range.

=LOOKUP(MAX(A1:D1)+1,A1:D1)

This will do the same thing but it contains an extra function that really
isn't needed.

Sometimes this gets into what I consider to be overkill. For example, I use
a lookup formula like this in my check register. I know for certain that the
numbers I'm dealing with are nowhere near 1E100 so I use a much smaller more
realistic number as my lookup value.

You might also see others use a lookup value of 9.99999999999999E+307. This
is also a very large number in scientific notation. It's the largest number
that Excel can handle.

While that will work just fine I think it confuses most users. How many 9s
do I enter? Let's see...1...2...3...4...5...6...7. Oh heck, I'll just use
1E100 and not have to bother counting 9s.

Suppose you were looking up bowling scores. You know for a fact that the
highest possible bowling score is 300. You can use one of these for your
lookup value:

301
9.99999999999999E+307

I'll choose 301.

99^99 (or other similar expressions) will also work. It *calculates* a very
large number to be used as the lookup value. But, if you don't need to use
an extra calculation why do it?