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
Bookmarks