I have an old complex formula (worked years ago) that involves this part of a formula LOOKUP(2,1/($G$28:$G30<>"" that can be dragged down to extend in the same column. It’s supposed to only update the cell if it’s changed, otherwise it’s left blank. Unfortunately it doesn’t work anymore due to a Google Sheets update.

The complete formula is:

=SWITCH(F31,$C$5,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)-E31,$C$3,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)+E31,$D$6,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)-E31,$E$6,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)-E31,$F$6,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)-E31,$C$12,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)+E31,$C$18,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)+E31,$C$24,LOOKUP(2,1/($G$28:$G30<>""),$G$28:$G30)+E31,"")

The LOOKUP(2,1 that is supposed to ignore errors, doesn’t ignore errors anymore. There are plenty of tutorials and google searches themselves that lead me to the same conclusion to use the exact same formula, but now it always errors out.

To reiterate, I only want cells in a column updated if they’re changed, otherwise they’re left blank (or “”) and if Google runs another update in a few years time, I don’t want an ad-hoc method hack-job of using a quirky error state in order to achieve the result.

Is there a proper way to have a column not repeat itself, and will be future-proofed against future versions of Google Sheets (as far as reasonably possible)?