Return value from cell of adjacent last empty cell

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
Hi all,

I need a formula for the below example:


Shivaji
Nagar

<tbody>
</tbody>
3Here "Paud Road" should come which
is beside the empty cell above 4

Paud
Road

<tbody>
</tbody>

Paud
Road

<tbody>
</tbody>

Kothrud

<tbody>
</tbody>
4Here "Kothrud" should come which
is beside the empty cell above 5

Kothrud

<tbody>
</tbody>

Kothrud

<tbody>
</tbody>

Kothrud

<tbody>
</tbody>

Viman
Nagar

<tbody>
</tbody>
5Here "Wagholi" should come which
is beside the empty cell above 3

Wadgaon
Sheri

<tbody>
</tbody>

Wadgaon
Sheri

<tbody>
</tbody>

Wadgaon
Sheri

<tbody>
</tbody>

Wagholi

<tbody>
</tbody>

B T
Kawade Road

<tbody>
</tbody>
3Here "Bekhrai" should come which
is the last there can also be a number below

Ramtekadi

<tbody>
</tbody>

Bekhrai
Nagar

<tbody>
</tbody>

<tbody>
</tbody>



















































Hope it is understandable.

There are multiply entries like these and entering manually is tiresome and time consuming.

Please need simple formula

Thanks

Jack
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Put this array-entered** formula in cell C1 and copy it down to the end of your data...

=IFERROR(IF(LEN(B1),INDEX(A:A,ROW(B1)+MATCH(1,--(LEN(B2:B$14)>0),0)-1),""),A$14)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

NOTE: The red numbers must be the row number for the last cell in Column A that contain data.
 
Upvote 0
Hi,

I used the formula but it shows only the last cell name not the one before the following number in B column

Thanks
 
Upvote 0
Sorry Thanks I first did not understand the Array Part but after doing it properly

it worked perfectly thank you very much Rick
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top