Function fails when computed reference is only one row.

[bird333]

The following function fails when a reference calculates to one row. =INDIRECT("$A"&MATCH(FALSE,INDEX(ISBLANK(INDIRECT("$A"&SUM(ROW())&":$A"&LOOKUP(2,1/(INDIRECT("$A$1:$A"&(SUM(ROW())))<>""),ROW($A$1:$A$3000)))),),0)+(SUM(ROW()))-1)

It works fine if I simply add "+1" to the calculated row. Works =INDIRECT("$A"&MATCH(FALSE,INDEX(ISBLANK(INDIRECT("$A"&SUM(ROW())&":$A"&LOOKUP(2,1/(INDIRECT("$A$1:$A"&(SUM(ROW())+1))<>""),ROW($A$1:$A$3000)))),),0)+(SUM(ROW()))-1)

Can someone explain why? How do I fix this? Thanks!

[Glenn Kennedy]

The first one evaluates to =A1. The second one evaluates to = A2. It seem a rather complicated way of doing things. Please post a sample sheet (see yellow banner) showing what you are trying to do!!!

INDIRECT and whole column references are an EXTREMELY bad idea. They are very inefficient.

[bird333]

The first one evaluates to =A1. The second one evaluates to = A2. It seem a rather complicated way of doing things. Please post a sample sheet (see yellow banner) showing what you are trying to do!!!

INDIRECT and whole column references are an EXTREMELY bad idea. They are very inefficient.

Added workbook.

[Glenn Kennedy]

You still didn't tell me what you want the formula to do!! At a guess, you want a blank-free list. This is a FAR better way to get one:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$1000)/($A$2:$A$1000<>""),ROWS(E$2:E2))),"")

copied down.

[bird333]

You still didn't tell me what you want the formula to do!! At a guess, you want a blank-free list. This is a FAR better way to get one:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$1000)/($A$2:$A$1000<>""),ROWS(E$2:E2))),"")

copied down.

Sorry about that. The formula finds the address of the first cell that is not empty and then returns the contents of that cell. When the range that is fed to MATCH is just one cell (i.e. range is for example A2:A2 but gets changed to just A2), it fails. However when the range is more than one cell it works fine. The second formula in my original post where I added "+1" was to test this theory. It has to have a range of 2 cells or more to work. Why? Is there a fix?

[Glenn Kennedy]

So, if you want to return the first non-blank cell in a range forget the big, unwieldy formula of yours...

=LOOKUP(2,1/(A1:A3000<>""),A1:A3000)

[Glenn Kennedy]

Senior moment (all too common these days!!):

=VLOOKUP("*",A:A,1,FALSE)

[bird333]

So, if you want to return the first non-blank cell in a range forget the big, unwieldy formula of yours...

=LOOKUP(2,1/(A1:A3000<>""),A1:A3000)

Thanks but that returns the last row number not the first contents. Do you have a theory as to why that part of the formula fails with a one cell range?

[Glenn Kennedy]

See Post 7!!

[bird333]

I found an explanation for this behavior which is here. https://stackoverflow.com/questions/...gle-cell-range. Can someone explain what IF({1},RANGE) is doing? Particularly the 1 in the braces. Why does this work?