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

Fabian 0810

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!

Paul Newcome

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.

Paul Newcome

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.

Fabian 0810

Thanks Paul!

Paul Newcome

Happy to help. 👍️