Find a country in a cell with multiple countries...

dhaley

Hello everyone,

We are trying to find a list of countries in one sheet that may be in a cell in a separate sheet. we have used the IF formula with CONTAINS in the criteria and this works if we are looking up a singular reference but not on an entire column. This would be fine if the countries remained static but they will be moving into different waves throughout the program. I hope the screenshots here help tell the story a bit better:

We want to return Yes or No in the Wave 1 cell if the country in the Wave 1 countries sheet is found in the Country cell in the same row:

Screen shot of the table we want the formula to be housed and the result brought back:

Screen shot of the country field from this same table where all of the countries for that record are listed:

Screen shot of the different table that will house of the Wave 1 countries

Value from the formula should be yes since all three are listed in that Country cell for that row:

Thank you all so much for your help

Dan

Intern98

=IF(CONTAINS(AND([Country List]:[Country List]),[Country]@row),"Yes","No")

dhaley

Hi Intern98, I tried this formula and it did not work. I changed the [Country List] to reference the separate table where the countries reside and the [Country] to the row in the sheet I want the "Yes" or "No" to return and received #INVALID DATA TYPE.