I have a google sheet with 2 columns, one has unique values the other one a list of several values; I need to know if all the values of my first column are somewhere in the second one
Thank you
| Column A | Column B |
|---|---|
| A | A, B, C |
| B | D, E, F |
| D | G,R, Y |
I tried with =COUNTIF(FILTER(B:B,A:A=ROW(A:A)),C:C) > 0 But I can't manage to write this formulae correctly
I expected the column A to be green if the value exists in column B
CodePudding user response：
You can use the COUNTIF function to check if each value in Column A is present in Column B. Here is an example of how to use the formula:
=IF(COUNTIF(B:B,A1)>0,"Exists","Does not exist")
This formula checks if the value in cell A1 is present in the range B:B, and returns "Exists" if it is found, and "Does not exist" if it is not.
You can then drag down the formula to the rest of the cells in Column A to check all the values. Alternatively, you can use the VLOOKUP function: =IF(IFERROR(VLOOKUP(A1,B:B,1,FALSE),0)>0,"Exists","Does not exist")
The above formula will return "Exists" if the value in A1 is present in Column B.
CodePudding user response：
With REGEXMATCH you'll be able to find the value in the next column. Try with:
=REGEXMATCH(B1,A1)*(A1<>"")
OPTION 2
Above was to checking if it appears in the corresponding row. To check the entire column with more than one value per row:
=REGEXMATCH(TEXTJOIN(",",1,B:B),A1)*(A1<>"")
