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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!