I’m trying to auto select the last row in a column in the case $A$9000, $F$9000 and $G$9000. I’ve tryied using INDIRECT but couldn’t get it to work.
Anyone got a good idea or a hint.
=IFERROR(IF(INDEX(‘Stock 23 Jan 23’!$A$2:$A$9000, MATCH(1, (F2=‘Stock 23 Jan 23’!$F$2:$F$9000) * (G2=‘Stock 23 Jan 23’!$G$2:$G$9000),0 ))=A2," ",“Changed”),“New”)
And Sorry if wrong category, seemed the best fit
I dunno if I’m totally following but you can use A2:A9000<>"" to return true if there’s something in the cell or false if there’s not. Then use the lookup function to approximately match, which will grab the last cell, and then return the value (you have to convert the TRUE/FALSE to a number/an error by dividing 1 by it).
For column A: =LOOKUP(2,1/(A2:A9000<>""),A2:A9000)
Similar trick to what Jason posted, except without a stated range.
https://exceljet.net/formulas/get-value-of-last-non-empty-cell
=LOOKUP(2,1/(range<>""),range)
As stated on the page, this opens an array with a million or so cell entries out of the stated column, so it does take some processing power, but… 