Index and match multiple sheets
Hi, I have an index and match formula set up for 1 sheet and it works great but how do I handle it if I want the index to look at data from multiple sheets to pull the correct information. My current formula is:
=INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0))
I would like the index to look at the site number on about 3 or more different sheets. Can this be done?
Comments
-
Is the site number unique? Can it appear on multiple sheets?
if it is unique I would use stacked iferrors
=iferror(iferror(INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)),INDEX({Site Number ref 2}, MATCH([Site ID ref2]62, {Site Name ref 2}, 0)),INDEX({Site Number ref 3}, MATCH([Site ID ref 3]62, {Site Name ref3}, 0))
-
Since we are matching on the Site ID, wouldn't THAT have to be the unique value for this formula to work, or am I reading things wrong?
-
yeah you're right. brain fart on my part. [Site ID] should be constant for all three instead of having the ref 2 and ref 3 that I added.
-
I would leave the ref 2 and ref 3. Since we are looking at three different sheets, if the Site ID is not found on sheet 1, it will throw a #NO MATCH error which will trigger the IFERROR and move on to sheet 2, and then to sheet 3 if needed.
Just wanted to make sure we were establishing the correct unique values to be referenced so that no one goes through the effort of changing things up unnecessarily.
The way this week has been for me already, I figured I had read it wrong. Haha.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!