How can I consider multiple Columns within criterion range from collect formular


With the follwing formular the criterion ist just in one column of the reference sheet:

=IFERROR(INDEX(COLLECT({NBR 2023 Standard Sets - Input Orders Clie Range 5}, {NBR 2023 Standard Sets - Input Orders Clie Range 9}, CONTAINS([Indiv. Set No.]@row, @cell), {NBR 2023 Standard Sets - Input Orders Clie Range 6}, CONTAINS("NLS 3", @cell)), 1), "---")

and it works fine.

Now I want the change the criterion range (see in bold) from only one column to multiple (5) columns in the reference sheet. Is this possible? because the criteron is going to appear in one of the 5 columns only, but it could be any of them.

Thanks in advance!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need to create a helper column on the source sheet to join each of the 5 columns together on a row by row basis and then reference that.

    You cannot have different sized/shaped ranges within the same function, so if most are a single column then you will need to reference a single column for all. If you need to reference something 5 columns wide, then all ranges will need to be 5 columns wide.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!