Mac: Issue with using Lookup to find last non-empty value in range

Hi all,

I have a weird issue when using the following formula to find the last non-empty value in a range:

=LOOKUP(2,(1/A:A<>""),A:A)

I understand how this formula works by creating an array or either 1s or DIV/0 errors and then failing to match with 2, taking the last 1 value. This formula has been working nicely on my spreadsheets for 2 years now. But all of a sudden it has started to do something weird. If I click on the cell the formula is in and press enter it stops taking the last non empty cell and takes the second to last, and I can't make it return to taking the last non empty cell again.

Also, if the range only has one non-empty value, then it suddenly started returning #N/A error, until I click on the cell and press enter to re-run the formula and then it works.

It never used to do this. And also it only affects certain instances of this formula but not others, and the affected ones changes randomly. I think it may be related to the values in the range themselves not been hardcoded but the result of an INDEX(A:A,MATCH(B,A:A,0)) formula or simply a cell reference to another tab. If I hard code the data in the ranges the LOOKUP formula works fine.

Is this an error in Excel? I'm using it on a Mac. It seems really strange.

Grateful for any insight.

Thanks,

Alex

Thanks,

I've uploaded a version with dummy data. If you go to cell M51 for instance, it should pull the last value from the range L13:L21. But if you click on M51 and then press enter it stops doing this and pulls the second to last value.

I think it may be something to do with the fact that data in the range L13:L21 is pulled from range J326:J1828 which as data that is the result of multiple calculations in other ranges.

Thanks for your reply, but I don't have any errors at all in any cell when I open the file on my mac (I wouldn't have uploaded a file with errors - but interestingly when I open the file on a windows machine I do get #N/A errors).

Screenshot 2019-04-17 at 14.21.25.png