Return value of cell adjacent to blank cell

[Deliverance]

Hey guys,

I have a sheet with two columns; first is payment due date, and the other is actual payment date. As seen in the attached screenshot.
I've been trying to find a formula to lookup the first blank cell in actual payment date, and return the value of the cell to its right (payment due date).

I hope it's clear.
Thanks in advance.
https://imgur.com/7VMC5ss

 

[Deliverance]

https://imgur.com/PpcWZUq

 

[Marcelo Branco]

Assuming Actual Pay (dates) in A2:A100; Payment Due (dates) in B2:B100, try

=INDEX(B2:B100,MATCH(9.99E+307,A2:A100)+1)
format as Date

M.

 

[Deliverance]

Thank you Marcelo

It didn't work. I might have applied it wrong. gave me #value error.

For future reference I had to take the long route by creating a third column with the formula

=IF(ISBLANK(N6),L6,"Paid")

 

[Marcelo Branco]

The formula should have worked.
Questions
Where exactly are your data (columns / rows)?
Are the dates real dates (numbers), not text?

M.

 

[Deliverance]

The formula should have worked.
Questions
Where exactly are your data (columns / rows)?
Are the dates real dates (numbers), not text?

M.

Yes sir the dates are not text for sure . And here is a bigger screenshot to clarify:
https://gyazo.com/5f27f9186b08180f668b46591bb3e3a5

Also the merged L and M columns are L.

 

[Marcelo Branco]

Sorry, but I do not understand your data configuration - columns starting from the right side; merged cells etc.
See if this simple example (without merged cells) helps


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Actual Pay​

[/TD]
[TD]

Payment Due​

[/TD]
[TD][/TD]
[TD]

Next Payment​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

2018/02/05​

[/TD]
[TD]

2018/02/05​

[/TD]
[TD][/TD]
[TD]

2018/05/05​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2018/03/05​

[/TD]
[TD]

2018/03/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2018/04/05​

[/TD]
[TD]

2018/04/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD]

2018/05/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD]

2018/06/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD]

2018/07/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD]

2018/08/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD]

2018/09/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD]

2018/10/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD]

2018/11/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD]

2018/12/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD]

2019/01/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD]

2019/02/05​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in O2
=INDEX(M2:M14,MATCH(9.99E+307,L2:L14)+1)

M.