Last Entry

  • Thread starter Thread starter Biff
  • Start date Start date
B

Biff

Hi Phil,

Here's another example that is much more compact. Credit
for this solution should be given to Aladin Akyurek.

=LOOKUP(9.99999999999999E+307,A:A)

I used a variation of Don's formula for this type of
problem many times until I read a post containing Aladin's
formula. Note however that the LOOKUP solution will only
return numbers and not text. Don's formula will return
either.

You might be thinking that the number used as the lookup
value is really big and you'd be correct. You can read
about it in this thread:

http://makeashorterlink.com/?A22B42D35

You can use a much smaller lookup number. If for example,
you know that the numbers your looking for generally fall
into a range, make the lookup value slightly larger.

=LOOKUP(5000,A:A)

Hope this helps!
Biff
 
Hi Aladin,

I really like this solution for it's simplicity. Using a
defined name will help lessen the 'wow' response if
someone see's 9.99999999999999E+307. Also, thanks for the
added details.

Biff
-----Original Message-----

[...]
You can use a much smaller lookup number. If for example,
you know that the numbers your looking for generally fall
into a range, make the lookup value slightly larger.

=LOOKUP(5000,A:A)
Click to expand...
[...]

Biff,

Better not to take that route. One can easily define BigNum as referring to

9.99999999999999E+307

via Insert|Name|Define.

Once BigNum is available, the following example uses are readily available
for references of *numeric* type:

=LOOKUP(BigNum,D:D)

=LOOKUP(BigNum,2:2)

=LOOKUP(BigNum,A2:A100)

=LOOKUP(BigNum,E2:N2)

LOOKUP can be seen as a contraction of INDEX and MATCH...

=INDEX(D:D,MATCH(BigNum,D:D))

etc.

The above formulas all fetch the last numeric value. With MATCH alone, one
can get the position in a refernce of the last numeric value...

=MATCH(BigNum,C7:C100)

This does not compute the row index of the last value. However, the row
index can easily be reconstructed:

=MATCH(BigNum,C7:C100)+CELL("Row",C7)-1

or

=MATCH(BigNum,C7:C100)+ROW(C7)-1

For references of type *text*, we have...

BigStr

defined as referring to:

=REPT("z",255)

Thus, =LOOKUP(BigStr,M:M) etc. will work the same way as the formulas with
BigNum.

[Note for Harlan: REPT(CHAR(255),255) fails to capture the last value in
{"a","b","z"}.]

Aladin

[...]


.
Click to expand...
 
Back
Top