Use array to filter multiple lists and produce one dynamic output list

[bmyslins]

Hello, this is my first post!

I pulled this great formula from exceltactics.com and it works great for filtering through a single source list to produce a dynamic output list. In particular I'm looking through a todo list for only items for today or before.

I have multiple todo lists and would like to adapt the below to pull from all of them into the one output list. Please help Thank you in advance.

'Car Data' is a todo list, but I have many todo lists... not sure how to adapt this.

{
=IFERROR(
INDEX('Car Data'!B$2:B$1156,
SMALL(
IF(
'Car Data'!$H$2:$H$1156<=Today(),
ROW('Car Data'!B$2:B$1156)-ROW('Car Data'!B$2)+1
),
ROWS('Car Data'!B$2:'Car Data'!B2)
)
)
,"")
}

 

[Mike Szczesny]

Can you use something like this? I will give just the basic structure of the formula. So, I incorporated 3 ranges in this formula. So just follow the logic of the formula

=IFERROR(IFERROR(IFERROR(INDEX($A$1:$A$10,SMALL(IF($E$1:$E$10<=TODAY(),ROW($A$1:$A$10)-ROW($A$1)+1),ROWS($H$3:H3))),INDEX($B$1:$B$10,SMALL(IF($F$1:$F$10<=TODAY(),ROW($B$1:$B$10)-ROW($B$1)+1),ROWS($H$3:H3)))),INDEX($C$1:$C$10,SMALL(IF($G$1:$G$10<=TODAY(),ROW($C$1:$C$10)-ROW($C$1)+1),ROWS($H$3:H3))))," ")

<tbody>
</tbody>