How to run an index collect referencing two different sheets?

Andrew Ryback

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)

Paul Newcome

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))

Paul Newcome

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))

Andrew Ryback

This works! Thanks, @Paul Newcome

Paul Newcome

Happy to help. 👍️