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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 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!