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?

Row #	Column A	Column B
1		Frank
2
3		Leslie
4
5		Joe

<tbody>
</tbody>

 

[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

	A	B
1	Frank	Frank
2	Frank
3	Leslie	Leslie
4	Leslie
5	Joe	Joe

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A1 =LOOKUP("zzzz",$B$1:B1) <tbody> </tbody>

<tbody>
</tbody>

 

[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!