Index Collect return a value depending on value of a cell

Justin Mauzy

I am trying to collect a value depending on the choice in a cell.

I have 2 sheets using the following columns:

• Incoming Issues
  • Site ID (Text field)
  • Division (Dropdown field with 2 options: External, Internal)
  • Site Name (Text field)
• Information Sheet
  • Internal Site ID (Text field)
  • External Site ID (Text field)
  • Site Name (Text field)

Here is Incoming Issues sheet:

Here is the Information sheet:

Here is what I need to do:

I want to Index Collect the Site Name on the Information Sheet and return the value to Site Name column on the Incoming sheet but I need to:

• Return a different value depending on the value of the division column in the Incoming sheet (Internal/External)
• If the value is Internal compare the Site ID (Incoming sheet) to the Internal Site ID column in the Information sheet. And then return the Site name.
• If the value is External compare the Site ID (Incoming sheet) to the External Site ID column in the Information sheet. And then return the Site name.

If I use Index Collect to just look at one column, it works fine:

=IFERROR(INDEX(COLLECT({Information Sheet Range 1}, {Information Sheet Range 3}, [Site ID]@row), 1), "")

I just cannot get it to work if I need to add multiple criteria...

Thank you in advance.

Paul Newcome

Try an IF statement to output a different range based on internal vs external.

=IFERROR(INDEX(COLLECT({Site Name}, IF(Division@row = "Internal", {Internal ID}, {External ID}), @cell = [Site ID]@row), 1), "")

Paul Newcome

Try an IF statement to output a different range based on internal vs external.

=IFERROR(INDEX(COLLECT({Site Name}, IF(Division@row = "Internal", {Internal ID}, {External ID}), @cell = [Site ID]@row), 1), "")

Justin Mauzy

As always, it works perfectly. Thank you again for the help.

Paul Newcome

Happy to help. 👍️