Return value from cell of adjacent last empty cell

jackt05

Board Regular
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

<tbody>
</tbody>

<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

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

 Wagholi

<tbody>
</tbody>

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

<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.

Thanks

Jack

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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.

Hi,

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

Thanks

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

it worked perfectly thank you very much Rick

Replies
1
Views
328
Replies
4
Views
444
Replies
3
Views
543
Replies
0
Views
1K
Replies
3
Views
344

1,196,078
Messages
6,013,301
Members
441,760
Latest member
Sharina

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.

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

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