Why would Cells(x,y).Value return empty for non-empty cells in Excel VBA?

I have an Excel VBA function. In a loop, I use Cells(x,y).Value to return the value of certain cells. But sometimes that returns empty, even the cell is not empty.

One example in my worksheet is Cells(11,4).Value. So I placed both Cells(11,4) and Cells(11,4).Value in Watches.

In Watches, drilling down into Cells(11,4) shows Text to be "42.71%", and Value2 to be "0.42708218178176", which is what I would expect.

But also in Watches, Cells(11,4).Value still shows "Empty".

Adding to the mystery, if I delete another watch, then Cells(11,4).Value will suddenly now be populated!

The obvious questions are:

• Why?
• What can I do to stop Cells(11,4).Value returning Empty?

Edited to add:The VBA is lengthy and irrelevant (i.e. if I strip it back to a single line, I get the same result).

More relevant is the formula contained in cell D11 (the cell I'm interested in). It's this:=(((D8+D9)/D5)^(1/(DATEDIF(D3,IF(D6>0,D6,LOOKUP(2,1/(D18:D9998<>""),$B18:$B9998)),"d")/365.25))-1)

That IF function returns two possible values: D6 or LOOKUP(2,1/(D18:D9998<>""),$B18:$B9998). (The latter is intended to get the value from column B for the last value in column D.)

If I put a value in D6 (so the IF statement doesn't do the LOOKUP), then Cells.Value returns the correct result. If there's no value in D6, and the IF statement returns the LOOKUP formula, then Cells.Value will return Empty.