Returning entire rows from multiple sheets into array based on lookup values in multiple columns

[bdorofey]

I am attempting to utilize the VSTACK and FILTER functions to return entire rows of data from multiple tabs into a single array based on names populated in either of two specific columns (D & E) on each tab; the result returns an error. The VSTACK function alone returns a stacked array, so I figure that the error is on the FILTER side. I’ve tried the following equation:

=LET(v,VSTACK(SchoolA!A1:I300,SchoolB!A1:I300,SchoolC!A1:I300),((FILTER(v,INDEX(v,,A1:A300)<>""))*(FILTER(v,INDEX(v,,D1:E300)=B1))))

Where:
Blank lines are to be removed by checking Column A
Lookup values are to be found by checking Columns D and E
Lookup value is located in Cell B1
Any help is greatly appreciated.

 

[KRice]

Once you've combined the three arrays with VSTACK, they do not have a column A,B,C,... designation. You can refer to individual columns using INDEX, but the row and column arguments must be numbers (or arrays of numbers), not range references. Also note that VSTACK is producing a 900 row array (not a 300 row array).
So, if you want the 1st column to exclude blanks AND you want both the 4th and 5th columns to each equal B1, then...

=LET(v,VSTACK(SchoolA!A1:I300,SchoolB!A1:I300,SchoolC!A1:I300),FILTER(v,(INDEX(v,,1)<>"")*(INDEX(v,,4)=B1)*(INDEX(v,,5)=B1)))

And if you want the 1st column to exclude blanks AND you want either the 4th OR 5th column (or both) to equal B1, then...

=LET(v,VSTACK(SchoolA!A1:I300,SchoolB!A1:I300,SchoolC!A1:I300),FILTER(v,(INDEX(v,,1)<>"")*(((INDEX(v,,4)=B1)+(INDEX(v,,5)=B1))>0)))