Index Collect return a value depending on value of a cell

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.
Best Answer
-
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), "")
Answers
-
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), "")
-
As always, it works perfectly. Thank you again for the help.
-
Help Article Resources
Categories
Check out the Formula Handbook template!