Table of Contents
Google Sheets provides a way to check if one column’s values exist in another column by using the COUNTIF function. This function can be used with two columns of data, and will return the number of occurrences of a value in one column that also exist in the other. This is a useful way to quickly check if values from one column exist in another.
You can use the following formula to check if one column value exists in another column in Google Sheets:
=NOT(ISERROR(MATCH(A2,$B$2:$B$16,0)))
This particular formula checks if the value in cell A2 exists in the range B2:B16.
If it does exist in the range B2:B16, the formula returns TRUE. Otherwise, it returns FALSE.
The following example shows how to use this formula in practice.
Example: Check if One Column Value Exists in Another Column in Google Sheets
Suppose we have the following dataset in Google Sheets where one column contains a list of groceries needed and the other column contains a list of groceries available at the store:
Suppose we would like to check if each item in the Grocery List column also exists in the Grocery Inventory column.
To do so, we can type the following formula into cell C2:
=NOT(ISERROR(MATCH(A2,$B$2:$B$16,0)))
We can then drag and fill this formula down to each remaining cell in column C:
Column C shows whether or not each cell in column A also exists in column B.
For example, we can see:
- Apples does exist in the grocery inventory.
- Bananas does exist in the grocery inventory.
- Carrots does not exist in the grocery inventory.
- Pears does exist in the grocery inventory.
- Peppers does not exist in the grocery inventory.
If you’d like to return values other than TRUE and FALSE, you can wrap the formula in an IF function and specify the values you’d like to return.
=IF(NOT(ISERROR(MATCH(A2,$B$2:$B$16,0))), "Yes", "No")
The following screenshot shows how to use this formula in practice:
The formula now returns “Yes” if the item in the Grocery List exists in the Grocery Inventory or “No” if it does not.