2D =FILTER formula to reference row count

[MurdochQuill]

Hello,

I'm defining my max rows via a basic lookup

=LOOKUP(8,1/('Data'!A:A<>""),ROW('Data'!A:A))

This will be named "Rownum" to call globally. So for instance Rownum = 345 or however long Sheet "Data" col A is.

I'm looking to change the hard-coded "z20" and "aa20" to still reference the original column but for the row number reference "Rownum", which in this instance would make it "z345" and "aa345"

=IFERROR(FILTER('Sheet1!A2:Z20,ISNUMBER(MATCH('Sheet1'!$AA$2:$AA$20,"YES",0))),"")

any ideas?

 

[MurdochQuill]

Sorry for the dumb question. All I required was an INDEX of the column to capture the max

Sorted.