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,A2:D2,A1:D1)

Result = Mar

Lookup the big num in A2:D2 and return the corresponding value from A1:D1.

--
Biff
Microsoft Excel MVP


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


"T. Valko" wrote in message
...
=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?

--
Biff
Microsoft Excel MVP


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