LookUp to Display Last Value Populated a in Range

[sony654]

I want to write a function that displays the last value populated ina range.
To help explain please see below:

column A B C D E F G
H
row
1 67 55 42 66 49 39 72
56
2 56 38 44 77 59
3
4

This is what I'm trying now, doesn't work.
=IF(COUNT(a1:h4),LOOKUP(99^99,a1:h4),"-")

I want to be able to display in this case the value 59.


I need to write a function that evaluates A1:H4 and displays the last value
populated. In this case 59.

 

[T. Valko]

*Maybe* this:

=IF(COUNT(A1:H4),LOOKUP(1E100,INDEX(A1:H4,MAX(INDEX((A1:H4<>"")*ROW(A1:H4),,)),)),"-")

It will return the last numeric value from top to bottom, left to right.

 

[sony654]

Biff, I just integrated the function below and got a #REF! error, and know
why. I didn't present the problem exactly right. The cell range is not
contiguous, so A1:h4 does not accurately reflect the cell range. The actual
cell ranges to "lookup" are:

b57:ae57,b64:ae64,b71:ae71

This non-contiguous range must be read to identify the most recent (or last)
value populated.

Can you please modify your function below to accommodate the non-contiguous
range above?

Thanks for your help. - Tom

 

[T. Valko]

Explain *exactly* how the ranges are filled. By that I mean:

Is one range filled contiguously then the next range is filled and then the
next ?

Or, do the ranges hold random amounts of data?

If the ranges hold random amounts of data is the data contiguous in the
range?

If the ranges hold random amounts of data which is the last number entered,
from left to right or from top to bottom? If it's left to right then row 57
has the last number. If it's top to bottom then row 71 has the last number:

row 57: 1,2,3,4,5,6
row 64: 1,2,3
row 71: 1,2,3,4

Are the numbers sequential so that maybe you can just look for a max/min?

Are they dates? Can we just look for the most recent date?

Help us help you by giving us *as much information as possible*.

 

[sony654]

Biff, Thanks and sorry for delayed response.

Actually each range is filled complete (on average one cell per day). And
all data entered is numbers (actually daily stock prices, ie. 25.625, etc.).

All cells in b57:ae57 first
then all cells in b64:ae64
then all cells in b71:ae:71 (it takes appx. 30 days per range).

and at any point in time, the function will extract the most recent (last)
cell populated. If b57:ae57 is filled, and b64:g64 is filled, the function
should return the value in g64.

Thanks for your patience and much appreciation. - Tom

 

[T. Valko]

I can't think of an "elegant" way to do this but the formula below will
work:

=IF(COUNT(B57:AE57,B64:AE64,B71:AE71),LOOKUP(1E+100,IF(COUNT(B71:AE71),B71:AE71,IF(COUNT(B64:AE64),B64:AE64,B57:AE57))),"")

 

[sony654]

Biff, thanks man, that worked great, much appreciated - Tom

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[sony654]

Biff, I got an error on this function (it just says worksheet function
contains error). And the word COUNT the last time it's displayed below is
highlighted. The function worked until I increased the lookup range to
include b92:ae92 (through b86:ae86 worked). I'm not sure why the error
exists. Please help. And the non-contiguous ranges I'm searching for the
last number displayed are also below.

=IF(COUNT($B$56:$AE$56,$B$62:$AE$62,$B$68:$AE$68,$B$74:$AE$74,$B$80:$AE$80,$B$86:$AE$86,$B$92:$AE$92),LOOKUP(1E+100,if(count($B$92:$AE$92),$B$92:$AE$92,IF(COUNT($B$86:$AE$86),$B$86:$AE$86,IF(COUNT($B$80:$AE$80),$B$80:$AE$80,IF(COUNT($B$74:$AE$74),$B$74:$AE$74,IF(COUNT($B$68:$AE$68),$B$68:$AE$68,IF(COUNT($B$62:$AE$62),$B$62:$AE$62,$B$56:$AE$56))))))),"")

b56:ae56
b62:ae62
b68:ae68
b74:ae74
b80:ar80
b86:ae86
b92:ae92

Thanks for your review and advice.

 

[T. Valko]

You've exceeded the maximum nesting level for functions which is 7.

Each row range starts in column B. Is there some kind of descriptive row
header for each of these ranges in column A? Like maybe a date?

 

[Domenic]

First define the following....

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=LOOKUP(BigNum,CHOOSE(MATCH(TRUE,COUNTIF(INDIRECT({"B92:AE92","B86:AE86",
"B80:AE80","B74:AE74","B68:AE68","B62:AE62","B56:AE56"}),"<>")>0,0),B92:A
E92,B86:AE86,B80:AE80,B74:AE74,B68:AE68,B62:AE62,B56:AE56))

Hope this helps!

 

[T. Valko]

This may be the "easiest" way to do this but requires the free add-in
Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Using named ranges for:

b56:ae56 = rng1
b62:ae62 = rng2
b68:ae68 = rng3
b74:ae74 = rng4
b80:ae80 = rng5
b86:ae86 = rng6
b92:ae92 = rng7

This formula can refer to up to 29 ranges.

=LOOKUP(1E100,ARRAY.JOIN(rng1,rng2,rng3,rng4,rng5,rng6,rng7))

The ARRAY.JOIN function creates a one dimensional vertical array from the
referenced ranges. The total size of this vertical array can not exced 65535
items.

 

[sony654]

Biff, Thanks, I have been away from my worksheet development for awhile. If
I add-in the function below will it reside in the file permanently?

Thanks

 

[sony654]

Domenic, Thanks. I have formula added and it seems to be woking