Lookup & Indirect to Find Last Non Blank

[anderb16]

Hello,

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!

 

[Fluff]

You're formula works for me, but how about

=LOOKUP(2,1/(B$2:INDEX(B:B,ROW()-1)<>""),A$2:INDEX(A:A,ROW()-1))