Making Filter Function dynamic and Control Spill to Rows only and not Columns

[rkaczano]

On Sheet 1 I have a range in C25:H181 which is pulling data from Sheet 2 via a Filter function which Spills.

=FILTER('Sheet 2'!$G$13:$L$251,'Sheet 2'!$AE$13:$AE$251>0)

On Sheet 1, in column I ("capital i"), I have some additional information that I need to pull from Sheet 2 to append to the table that I created with the filter function and I am using a simple INDEX/MATCH to do so in that column. The formula in I25 is as follows:

=INDEX('Sheet 2'!$AE$13:$AE$251,MATCH($C25,'Sheet 2'!$G$13:$G$251,0),1)

Two Questions:
1) How do I make the Filter function dynamic as new data is added to Sheet 2? I cannot change this formula to =FILTER('Sheet 2'!$G:$L,'Sheet 2'!$AE:$AE>0) as there is data in rows 1 to 12 on Sheet 2 that I don't want pulled into the Filter function. Should I simply name the data range in Sheet 2 with a dynamic named range (i.e. 'Sheet 2'!$G$13:$L$251)?

2) I also want the Index/Match functions in column J on Sheet 1 to be dynamic. Changing the $C25 to $C25# does not work as it also wants to spill down AND to the right of column J - but I only want it to spill down. INDEX('Sheet 2'!$AE$13:$AE$251,MATCH($C25#,'Sheet 2'!$G$13:$G$251,0),1). Is it possible to restrict a spill to rows only?

Thank-you

 

[Fluff]

For the 1st part of your question, you can use

=FILTER('Sheet 2'!$G$13:$L$1000,'Sheet 2'!$AE$13:$AE$1000>0)

change the 1000 to any number of rows (within reason) that should always cover your data.

For part2, try

=INDEX('Sheet 2'!$AE$13:$AE$251,MATCH(Index(C25#,,1),'Sheet 2'!$G$13:$G$251,0),1)

 

[rkaczano]

Great thanks - I have never used and Index inside a Match before - good to know!

 

[Fluff]

You're welcome & thanks for the feedback.