Index/Match multiple results in multiple sheets

Question

As the title suggests I'm looking to do an Index/Match in multiples sheets but to also return multiple results.

I know how to return multiple results with

=IFERROR(INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$A$1:$A$100=myValue,ROW(Sheet!$A$1:$A$100)),ROWS(1:1))),"")

and then copy it down.

I also know how to look through multiple sheets for a unique value

=IFERROR(INDEX(Sheet1!B$1:B$100,MATCH(myValue,Sheet1!$A$1:$A$100,0)),INDEX(Sheet2!B$1:B$100,MATCH(myValue,Sheet2!$A$1:$A$100,0)))

I was wondering if there was a way to do this with multiple values in multiple sheets ? Combining the two formulas in some way ?

My problem is that because I do not know when the switch between the two sheets will be made in the search, I cannot offset it by the correct number of rows in the first formula.

Thanks to all of you !

Answer 1

Thanks Jeeped, it was exactly that ! Just needed to count the matched in the other sheets to reset the rows.

The final formula looks like this

=IFERROR(IFERROR(INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$A$1:$A$100=myValue,ROW(Sheet!$A$1:$A$100)),ROWS(1:1))),INDEX(Sheet2!B$1:B$100,SMALL(IF(Sheet2!$A$1:$A$100=myValue,ROW(Sheet!$A$1:$A$100)),ROWS(1:1)-COUNTIF(Sheet1!$A$1:$A$100,myValue))),"")