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
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!