Best Formula to use for Validation between 2 Columns in 2 Sheets

ConnorForm

Good Day,

Looking for some ideas/suggestions for a formula to use to verify values placed in a column in a sheet. In my scenario, I have Sheet 1 and Sheet 2. Within this 2 sheets are 2 columns that need to match. Sheet 1 - Column: 'Product Category' is a manually entered field. This column/field needs to match or flag when it is not matching Sheet 2 - Column: 'product_category', Sheet 2 is essentially a lookup file to assist with data processing with performance data on the backend. So that is why We need a validation essentially that says that we only have current live product categories in sheet 1 and that flags anything that is not on that product look up sheet (sheet 2).

Sheet 1 - Product Category

Sheet 2 - Product_Category

ConnorForm

Another layer to this…. some fields entered in sheet 1 are multiple products separated by a |, however in sheet two they are there own row so I might need a helper column or include the 'has' function if possible

Sheet 1

Sheet 2 does not have | but the products will be their own row

jmyzk_cloudsmart_jp

Hi @ConnorForm

As shown in the image below, you can create a range for the HAS function by substituting "|" with CHAR(10) in a multi-select dropdown helper column, like PC in this example.

https://app.smartsheet.com/b/publish?EQBCT=f3d7895f89a2449abb8245e9663181b5

https://app.smartsheet.com/b/publish?EQBCT=40a92a13e4a24f3da0ce4d767d5936d3