# Index plus match with multiple tabs

#### mgeiss

##### New Member
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)))

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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)))

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

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.

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

Replies
2
Views
344
Replies
3
Views
271
Replies
3
Views
139
Replies
2
Views
65
Replies
2
Views
485

1,196,042
Messages
6,013,045
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back