Lookup Value not working

[CodyMonster]

=LOOKUP(2,1/(sheet1!A3&":"&ADDRESS(MATCH(K4,sheet1!E:E,0),1,4,1,)<>"0"),sheet1!A3&":"&ADDRESS(MATCH(K4,sheet1!E:E,0),1,4,1,))

What am I doing wrong here?
It should work? No?

 

[live_excel]

Your vlookup is not in exact format.

 

[CodyMonster]

It isn't a vlookup I'm trying to find the last non blank field in a range. But the range is based off the match. From A3:A(the row in the match function).
For some reason it's bombing out.

 

[CodyMonster]

If I change the formula to
=LOOKUP(2,1/(sheet1!A3&":"&ADDRESS(MATCH(K4,sheet1!E:E,0),1,4,1,)<>"0"),sheet1!A:A)

I get the first non-blank cell.. not what I'm looking for.
The prior formula would bring back #value

What I'm trying to achieve:


A 10
20
30
B 40
50


My match function is looking for the row in col B. For example I'm looking up 30 I want to return A.
If I was looking for 50 it would return B. I was trying to keep the range bound by where the number is found.
If I was looking for 30 I would keep the range for the lookup at A1:A3. Looking for 50 the range would be A1:A5.
That way when its looking for the last blank cell it would only pick up what is in the range.