How to run an index collect referencing two different sheets?

Hi everyone,
I'm trying to run an Index Collect formula that references two different sheets.
I want to pull in an email address based on an individual's name and position. The formula needs to reference our internal contractor database, as well as our staff list.
Below is what I've entered, but I'm getting an unparseable error.
Any thoughts?
=INDEX(COLLECT({BMG MASTER CREWING DATABASE_1}, {BMG MASTER CREWING DATABASE Range 1}, RESOURCE@row, {BMG MASTER CREWING DATABASE Range 2}, NAME@row), 1), INDEX(COLLECT({BMG STAFF LIST EMAIL}, {BMG STAFF LIST Range 1}, RESOURCE@row, {BMG STAFF LIST Range 2}, NAME@row), 1)
Best Answer
Answers
-
Try this:
=IFERROR(INDEX(COLLECT({BMG MASTER CREWING DATABASE_1}, {BMG MASTER CREWING DATABASE Range 1}, RESOURCE@row, {BMG MASTER CREWING DATABASE Range 2}, NAME@row), 1), INDEX(COLLECT({BMG STAFF LIST EMAIL}, {BMG STAFF LIST Range 1}, RESOURCE@row, {BMG STAFF LIST Range 2}, NAME@row), 1))
-
This works! Thanks, @Paul Newcome
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!