Indexing with mutiple results - dynamic source

[doofusboy]

Thanks to picking the brains of the good folks on this board through searching previous threads, I was able to come up with a formula that returns multiple results when indexing rather than just the first match.

Now the problem I have is that my source table is going to vary in size. My source table in the formula I got working below is a dynamically named range 'ImportedData'. The data in this table will always start on Row 8 but the last row of the table will vary, so the bold/red areas of the formula need to be dynamic.

Can anyone suggest how I might modify this formula to allow for the dynamic table size?

=IF(ROWS(B$19:B19)<=L$19,INDEX('Empower Data'!$D$8:$D$19,SMALL(IF('Empower Data'!$F$8:$F$19=E$17,ROW('Empower Data'!$D$8:$D$19)-ROW('Empower Data'!$D$8)+1),ROWS(B$19:B19))),"")

Any help is appreciated.

 

[doofusboy]

OK, got it sorted. Gave a range name to the first cell of each column in the source table and used that in place of the absolute cell reference.

Life is good and thanks once again Peter, your help is appreciated.