INDEX/MATCH function across more than 2 sheets
Hi,
I have a list of sites and I am try to auto populate the Site type column based on different sheets (Site Index sheet... but each country has one)
I have the formula working to reference just Australia's Site Index Sheet but I need to INDEX all of the countries sheets ...
=INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0))
I tried the below but that doesn't ork:
=INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0)) + INDEX({CEE - Ops Site Index BL}, MATCH([Site Name]@row, {CEE - Ops Site Index Sitename}, 0))
Answers
-
You can use IFERROR to do this.. IFERROR(value, value_if_error).
Something like this
= IFERROR(INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0)),IFERROR(INDEX({CEE - Ops Site Index BL}, MATCH([Site Name]@row, {CEE - Ops Site Index Sitename}, 0)),IFERROR(INDEX.....and so on )
It will try to search for the SiteIndex in Australia and if it doesn't find it in the sheet then it will through "#NOMatch error, so we will put the Index formula for the next BL sheet in the "value_if_error" place. You can keep on doing it for as many sheets you want
Parul Mishra
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!