Lookup last value using a dynamic, non-named ranged

Hello,

I don't quite understand how a formula that starts with ="Y"& can return L43, but in any case: Replace the L4 reference in indirect(L4) with the formula that is in L4. That won't look pretty and may be really, really hard to troubleshoot in a few weeks, once you have forgotten what you were trying to do, but you don't need a named range.

I'd use exactly the opposite approach: Use helper colums and named ranges for everything, because that will speed up calculation and will make auditing and troubleshooting/maintenance a lot easier.

cheers, teylyn

Originally Posted by JayCee18

I'm trying to find the last available data in a dynamically sized column using the lookup function.

=LOOKUP(2,1/(NOT(ISBLANK( '1'!Y12: (indirect(L4)))),'1'!Y12: (indirect(L4))

The contents in indirect L4
="Y"&IFERROR(MATCH(A4,'1'!$C$12:$C$130,0),MATCH(A4,'1'!$C$12:$C$130,1))+12-1
which returns a cell reference, L43

Are you saying that the LOOKUP function should evaluate like this:

=LOOKUP(2,1/(NOT(ISBLANK('1'!Y12:L43))),'1'!Y12:L43)

If so, that formula won't work on a 2D range.