Using parameters in ms excel filter function

Question

I need to select multiple values from a table whose size can vary. So currently i have hardcoded the range in the FILTER function. I need to parameterize the range. Any idea how it works?

I tried to keep the numerical value in a separate column and used & to call this, but its showing error. Please help.

I am using something like this currently,

=filter(sheet2!A1:J10,sheet2!C1:C10=sheet1!A2,”not found”)

Here, the number of records in Sheet2 can be variable. Currently it is 10. I took the COUNTA(Sheet2!A:A) in a “column L” in sheet 1 and tried to use it as parameter but didn’t work.

=filter(sheet2!A1:J&L1,sheet2!C1:C&L1=sheet1!A2,”not found”)

Answer 1

You can use this formula:

=LET(d,Sheet2!A:J,
cntRows,COUNTA(CHOOSECOLS(d,1)),
data,TAKE(d,cntRows),
FILTER(data,INDEX(data,,3) = sheet1!A2, "not found"))

It stores the number of filled rows in cntRows, creates the used range in data and does the filtering