Best way to use lookup to find last value in column of another sheet

[bostenson]

Using a lookup function to find the last value in a column based on a value in an array where the function is written, I have used the following function:

'=LOOKUP(2,1/($A3&"!F:F">0),$A3&"!F:F")

A3 is the vertically anchored cell I am using that references the sheet name where I want to execute the lookup while column F stores the values that I want the function to return. The result I am receiving is, #VALUE! The question is, is there a better way to execute this procedure without drawing the error. I have attached an example file. Thanks in advance.

28018


- Ben

[p45cal]

In G2 copied down, try:

=LOOKUP(2,1/(--NOT(ISBLANK(INDIRECT("'" & $A2 & "'!F1:F9999")))),INDIRECT("'" & $A2 & "'!F1:F9999"))
this uses NOT(ISBLANK( rather than >0 in case the last value is negative.
It also limits how many rows are taken into account using F1:F9999 rather than F:F to save on recalculation time; adjust this 9999 to a coverall value that will never be exceeded on those other sheets.

[snb]

I use:


=INDEX(OFFSET(INDIRECT(ADDRESS(1;6;1;1;A2));;;2^10);MATCH(10^8;OFFSET(INDIR ECT(ADDRESS(1;6;1;1;A2));;;2^10);1))

[bostenson]

Thanks guys, these will help.


- Ben