Confusion on LOOKUP function

[Walter Mayes]

Yesterday I asked how to retrieve the last value in a dynamic column of
values. Aladin Akyurek gave the following formula which does exactly as I
requested:

=LOOKUP(9.9999e+307,G:G)

I had not considered the LOOKUP function as it has been my understanding
that the LOOKUP functions will look for a value "match" and if none is found
it will scan the list until it finds a value greater then the requested
value then return the value in the previous cell.

EXAMPLE: If I have numbers from 16000 to 18000 and I ask LOOKUP to
retrieve 17000, it would retrieve the 17000 if this number appears in the
list. But if 17000 does not appear in the list, LOOKUP will scan down my
list until it sees the first value above 17000 (17100) and then return the
value that appears in the cell above the 17100 value. (possibly 16941)
Why does the "BIG NUMBER" in Aladins formula cause the LOOKUP function
to go to the last value entered? I'm thinking all values in my list are less
than the requested value (BIG NUMBER) and the "next" cell after my last
entry is "empty" (therefore "nothing"). So what is the logic that causes
LOOKUP to retrieve the last entry as opposed to any other entry?
I have tried the VLOOKUP function following Aladins suggestion and it
also retrieves the last entry.
Microsoft HELP states at one point, "With HLOOKUP and VLOOKUP, you can
index down or across, but LOOKUP always selects the last value in the row or
column." This is confusing to me.
Hope I have explained my question clearly.

Walter Mayes

 

[Don Guillett]

The lookup will find the highest number that is NOT interrupted by a letter.
If you want to find out which is the last row in the column use match with
any number that is larger than the largest number possible. This ignores
intervening text.
=MATCH(999999999999,D)

 

[Dave R.]

I think just found a 'bug' in Microsoft's help file;

The sentence:
If LOOKUP can't find the lookup_value, it uses the largest value in the
array that is less than or equal to lookup_value.

says if lookup can't find it, it will "use" either 1)largest value less than
lookup value OR 2)the value it cannot find.

How it's going to "use" the value it can't find, if it can't even find it,
is puzzling.

 

[Walter Mayes]

Thanks Don. I didn't need to find the last row, I needed to find the
last "value" and couldn't quite understand exactly how the lookup function
preformed.
However, every little bit of information I can glean from this group is
helpful. I wasn't aware that the "match" function would return the last row
when used in this way. THANKS

Walter Mayes

 

[Dave R.]

I have read the online help for LOOKUP, and find the language a bit
confusing (see for example my other post in this thread).

Here's what the help says:
________________
The array form of LOOKUP looks in the first row or column of an array for
the specified value and returns a value from the same position in the last
row or column of the array. Use this form of LOOKUP when the values you want
to match are in the first row or column of the array. Use the other form of
LOOKUP when you want to specify the location of the column or row.

If LOOKUP can't find the lookup_value, it uses the largest value in the
array that is less than or equal to lookup_value.

__________________

From playing with it, I would just guess that the sentence "If LOOKUP can't
find the lookup_value, it uses the largest value in the array that is less
than or equal to lookup_value" is written wrong, and should instead say
something like;

"If LOOKUP can't find the lookup_value, it uses the deepest element in the
array the array that is less than lookup_value."

(I also took out the "or equal to" since it doesn't apply..).


The clarification is that it doesn't find the highest number or the largest
value (if that means the max value in the range), as the instructions, and
Don say, it USES (i.e., USE for looking up one value and returning the value
from the last row/column) the value which is less than the lookup value that
is furthest down in the array.

Can anyone shine light on this?

 

[Walter Mayes]

I'm thinking,(which is not good) and after playing with LOOKUP and
VLOOKUP that if the function finds the value you have asked for, it returns
that value. If it does not find the value you asked for but finds a larger
value, then it returns the value of the previous cell. If it does not find
the value or any value larger then you asked for, it then returns the "last"
value in the range.

Walter Mayes