how to make my array formula dynamic

[danhendo888]

INDEX(Sheet2!$L$3:$L$20000,SMALL(IF(FREQUENCY(IF(Sheet2!$K$3:$K$20000=$B2,Sheet2!$L$3:$L$20000),Sheet2!$L$3:$L$20000),ROW(Sheet2!$L$3:$L$20000)-ROW(Sheet2!$L$3)+1),1))

I have a workbook with two sheets.
Sheet 1 has the above formula which points to Sheet 2.
Sheet 2 is where I paste my data.

I update my data everyday.
Notice that the row range is from row 3 to row 20000
Suppose I paste new data and the rows in Sheet 2 extend to row 40000.

Is there a way for the formula to update automatically to say, for example, L3:L20000 where ever underlined?

 

[Scott T]

You could use dynamic named range

https://www.excel-easy.com/examples/dynamic-named-range.html

 

[danhendo888]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Thanks Scott,

Tried the below

but it's causing a lot of lag to my workbook (several tens of thousands of rows) as OFFSET is a volatile function.
Will look for another way to do dynamic name range.
​

ColumnK =OFFSET(Sheet2!$K$2,,,COUNTA(Sheet2!$K:$K)-1)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

ColumnL =OFFSET(Sheet2!$L$2,,,COUNTA(Sheet2!$L:$L)-1)​

[/FONT]

=INDEX(ColumnL,SMALL(IF(FREQUENCY(IF(ColumnK=$B2,ColumnL),ColumnL),ROW(ColumnL)-ROW(Sheet2!$L$3)+1),1))


​