Formula Solution - Look-up last value from this row or above

[southern5footer]

I'm trying to find a non-vba solution to this problem and I've searched and searched online but I think I might not know the correct terms to search effectively.

I want to populate every row of column A with the correct name in column B. so A1&A2 are Frank, A3&A4 would be Leslie and A5 would be Joe.

Is there a formula that could look-up starting in the row your in going up until it gets a value?

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Row #[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Frank[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Leslie[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Joe[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

maybe something like...

=LOOKUP("zzzz",$B$2:B2)

 

[southern5footer]

Thank you for your reply and for excusing my typos that I now notice. (I do know the difference between your and you're, I promise!)

The lookup function is where I went first but I won't know the value I'm looking up and therefore I'm a bit stuck.

Assuming I'm in A4. I need to lookup starting in B4, then B3 where I would find "Leslie" and in A4 the result would be "Leslie". Maybe it should be lookup combined with isblank.

 

[Weazel]

I'm not entirely sure I'm following what you are trying to achieve or I'm just misunderstanding what you need.

the lookup in A1 will simply return the name in column B until the next populated cell in column B.

Unknown[TABLE="class: grid, width: 200"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Frank[/TD]
[TD]Frank[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Frank[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Leslie[/TD]
[TD]Leslie[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Leslie[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Joe[/TD]
[TD]Joe[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=LOOKUP("zzzz",$B$1:B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[southern5footer]

What do you envision "zzzz" to be? I'm not attempting to be difficult just misunderstanding.

 

[btorrean]

How about the following formulas in column A:

Formula in A1
=B1

Formula in A2, and copied down:
=IF(B2="",A1,B2)

Brian

Brian J. Torreano

 

[southern5footer]

Yes, that works (THANKS!) unless there are more than 1 space between. There could be 10 or 20 empty cells. It might have to be vba, or a very very large nested statement!?

 

[Weazel]

zzzz would just really be a large text string, ideally it assumes you wouldn't have any text values larger then that.

very similiar to a very large number except for letters.

I suppose a more 'professional' way of writing it would be =LOOKUP(REPT("z",255),$B$1:B1) which would repeat the letter z 255 times.

this works in conjunction with how the Lookup function handles data because it only does approximate match.

 

[southern5footer]

Wait, not, that actually works. Thank you both!

 

[T. Valko]

I suppose a more 'professional' way of writing it would be =LOOKUP(REPT("z",255),$B$1:B1) which would repeat the letter z 255 times.

No, nothing wrong with "zzzz". I typically use "zzzzz".

The lookup value simply needs to be larger than any value in the lookup range. PERIOD!