CODEWITHSUNDEEP
excelexcel-formula
Zanderset
Zanderset

Reputation: 43

Index/Match multiple results in multiple sheets

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 !

Upvotes: 2

Views: 3127

Answers (2)

EUGENE
EUGENE

Reputation: 1

=IFERROR( INDEX( [RPTPOSTING.xlsx]BARCAM004!$B$7:$B$5000
                ,SMALL( IF( $B$3=[RPTPOSTING.xlsx]BARCAM004!$CE$7:$CE$50000
                           ,ROW([RPTPOSTING.xlsx]BARCAM004!$J$7:$J$50000)-6
                           ,"")
                       ,ROW()-6) )
         ,( INDEX( [RPTPOSTING.xlsx]ANINGWAY-SACA001!$B$9:$B$5000
                  ,SMALL( IF( $B$3=[RPTPOSTING.xlsx]ANINGWAY-SACA001!$CE$9:$CE$50000
                             ,ROW([RPTPOSTING.xlsx]ANINGWAY-SACA001!$J$9:$J$50000)-6
                             ,"")
                         ,ROW()-6) )
         ,"NO RECORD") )

Upvotes: 0

Zanderset
Zanderset

Reputation: 43

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))),"")

Upvotes: 2

Related Questions