LOOKUP & MATCH syntax ??

[Bassman62]

Re: article from Gord "Last Line" microsoft.public.excel.worksheet.functions
4/7/09
And from Biff "Need help finding the last cell w/data" 4/6/09
Sorry, I don't know how to accuire the link to the threads.

Ok Ya'll,
I see this syntax used in LOOKUP & MATCH; FUNCTION(99^99,Range).
I can test and see that it works but I'd like to know how it works in order
to take better advantage of it.
The Microsoft documentation says that the first argument is a Lookup Value
but I don't understand the logic of how it's being used in the examples
above to find the last used cell or last Number entered in a range.
Can someone point me to documentation that may help me better understand
this usage.
Thank you.

 

[Sheeloo]

=LOOKUP(99^99,A:A)
tries to find 99 to the power of 99 which is a very very large number and
unlikely to be in your lookuprange...so it tries to find it in the range and
returns the last value it finds... which it presumes to be the largest since
lookup range is supposed to be sorted...

 

[T. Valko]

=LOOKUP(1E100,A11)

...........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(A11)+1,A11)

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?

 

[Bassman62]

Thank you very much.
With this info, I re-read the MS docs and think I'm beginning to get it.
MS doc. "...and return a value from the - same position - ..." (last
position found with a value)
I shall carry on.
Thanks again.

 

[Gord Dibben]

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


Gord

 

[T. Valko]

To return a value from the same position in a different range:

Same principle, you just include the range where the result will come from.

...........A..........B..........C..........D
1......Jan........Feb.......Mar......Apr
2...................10...........7..............

=LOOKUP(1E100,A22,A11)

Result = Mar

Lookup the big num in A22 and return the corresponding value from A11.

 

[Bassman62]

Thanks very much to you all.
This helps a great deal.