=Index(Collect(... Contains

Holli Clendenin

Hello. I'm trying to return the "Flag" onto 'PS Bids Worksheet' from the 'Vendor File Nomenclature' (See 2nd snip below with blue highlight) page IF the Primary Column on 'PS Bids Worksheet' CONTAINS the description located on 'Vendor File Nomenclature', and it works great if there's not any additional words and/or parenthesis. Below is the formulas I'm using - what am I missing?

IFERROR(INDEX(COLLECT({Vendor File Nomenclature hCode}, {Vendor File Nomenclature hCode Description}, CONTAINS([Primary Column]@row, @cell)), 1), "Not Branded")

KPH

Hi Holli

Have I understood correctly.

• You have a formula in the right hand column of the first screen shot that is returning the Hospitality type (hCode) from the second screen shot?
• The formula you are using is
• =IFERROR(INDEX(COLLECT({Vendor File Nomenclature hCode}, {Vendor File Nomenclature hCode Description}, CONTAINS([Primary Column]@row, @cell)), 1), "Not Branded")
• This works for the example in green but not the one in yellow.

This is because of the way CONTAINS works. CONTAINS is checking Vendor File Nomenclature hCode and looking for any rows that CONTAIN the value from Primary Column in the current sheet.

So "Residence Inn" in the primary column matches "Residence Inn" in the second sheet and "RES" is returned. It would also match "Residence Inn and Suites", "The Residence Inn", etc. Anything in the second sheet that contains "Residence Inn" would be a match.

"Residence Inn (Agave)" in the primary column will not match with anything as there are not any rows in the second sheet that contain that string. You have "Residence Inn" but that does not contain "Residence Inn (Agave)".

Does that make sense? I think you are trying to do the opposite of what the CONTAINS function does, and rather than looking for rows that contain a string you want rows that contain a string that is contained in the original sheet.

The solution might be to add a column to your first sheet that uses a formula to extract part of the name (maybe everything up to the first parenthesis), and use that to match.