Hello,
I have data like this:
What I'm doing is returning the value in column A in the last non blank row in column B regardless of the value.
Currently, in cell B5, I am using =LOOKUP(2,1/($B$2:$B$4<>""),$A$2:$A4) which correctly gives me "Two."
I have multiple individuals using this spreadsheet, manipulating data as they need and they frequently break the formula.
What I tried:
In Cell B5 =LOOKUP(2,1/(INDIRECT(ADDRESS(2,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))<>""),INDIRECT(ADDRESS(2,1)&":"&ADDRESS(ROW()-1,1)))
Which gives me an #N/A result
However, using:
In cell B5 =LOOKUP(2,1/(INDIRECT(B6)<>""),INDIRECT(ADDRESS(2,1)&":"&ADDRESS(ROW()-1,1)))
With a helper in B6 =ADDRESS(2,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Provides the correct answer.
Is it possible to do this without the helper?
Thanks!
I have data like this:
Color | Shape | |
One | Circle | |
Two | Red | |
Three | Square | |
Two | Three |
What I'm doing is returning the value in column A in the last non blank row in column B regardless of the value.
Currently, in cell B5, I am using =LOOKUP(2,1/($B$2:$B$4<>""),$A$2:$A4) which correctly gives me "Two."
I have multiple individuals using this spreadsheet, manipulating data as they need and they frequently break the formula.
What I tried:
In Cell B5 =LOOKUP(2,1/(INDIRECT(ADDRESS(2,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))<>""),INDIRECT(ADDRESS(2,1)&":"&ADDRESS(ROW()-1,1)))
Which gives me an #N/A result
However, using:
In cell B5 =LOOKUP(2,1/(INDIRECT(B6)<>""),INDIRECT(ADDRESS(2,1)&":"&ADDRESS(ROW()-1,1)))
With a helper in B6 =ADDRESS(2,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Provides the correct answer.
Is it possible to do this without the helper?
Thanks!