Index plus match with multiple tabs

[mgeiss]

Hi there,

Below is a formula I’m working on. I want, if possible, for excel to look in tabs "acctg" through "RM finish Bundling" for the text that i have in '2015 Consolidated!A96'. The formula works if i only use one tab, but if I could have it look through all those tabs, it would help me significantly. Can i do that with an index/match formula? What am I doing wrong? Any help is appreciated.</SPAN>

=IF(IFNA(MATCH(A96,'Labor & Depr.'!B:B,0),0),INDEX('Labor & Depr.'!X:X,MATCH('2015 Consolidated'!A96,'Labor & Depr.'!B:B,0)),INDEX('Acctg:RM Finish Bundling'!X:X,MATCH('2015 Consolidated'!A96,'Acctg:RM Finish Bundling'!B:B)))

 

[BiocideJ]

To my knowledge, Index/Match formulas are NOT compatible with 3D (multisheet) ranges like you are trying to use.

So long as there are just those three tabs, I believe this substitute formula will do what you are looking for by searching in each tab successively.

=IF(ISNA(MATCH('2015 Consolidated'!A96,'Labor & Depr.'!B:B,0)),IF(ISNA(MATCH('2015 Consolidated'!A96,acctg!B:B,0)),IF(ISNA(MATCH('2015 Consolidated'!A96,'RM Finish Bundling'!B:B,0)),"NOT FOUND",INDEX('RM Finish Bundling'!X:X,MATCH('2015 Consolidated'!A96,'RM Finish Bundling'!B:B,0))),INDEX(acctg!X:X,MATCH('2015 Consolidated'!A96,acctg!B:B,0))),INDEX('Labor & Depr.'!X:X,MATCH('2015 Consolidated'!A96,'Labor & Depr.'!B:B,0)))

 

[mgeiss]

Darn. thanks for letting me know. There are actually 40+/- tabs. do you know of a formula that would search that many tabs?

 

[BiocideJ]

Nothing directly.
A work-around could be to create a vlookup on each tab in a cell somewhere outside of the used range (e.g. Z1) that returns the value in col X (if it exists) and "" if not
i.e. =IFERROR(INDEX(X:X,MATCH('2015 Consolidated'!A96,B:B,0)),"")
And then where you were doing the original 3D Vlookup something that concatenates all of those cell values.

i.e. =Sheet1!Z1&Sheet2!Z1&...&Sheet40!Z1

not a very elegant solution but it might be the easiest way to do what you require.

 

[mgeiss]

Ok, thanks for your help. I'm going to try those two.