How to find if text exists in other column in Google Sheets?

by

To check whether text in one column exists in other column you can use the following formula:

=if (countif(lookup_range,value)>0,"+","-")

for example:

=if (countif($A$3:$A$13,D3)>0,"+","-")

Check for text in another sheet or column

Step #1: Prepare your data

Gather your data in a Google Sheets worksheet and identify the lookup range (could be one or more columns located in your spreadsheet or another in the same file).

In our example we will use the following data.

We have three columns:

  • A lookup column / range.
  • Values – the text we would like to search for.
  • Covered – which will show whether the lookup values can be found in the lookup range.

Step #2: Write your formula

We will write the following formula in cell D3 and apply it by auto filling until cell D10.

=if (countif($A$3:$A$10,C3)>0,"+","-")

Explanation

  • We use an IF function that evaluates a logical condition.
  • The condition we’ll check if built using the COUNTIF function, which allows to count occurrences in which the condition is met – in our case that the value is found in the lookup range.
  • If the value is found, a + (plus) sign is written, otherwise a – (minus) sign.

Step #3: Check your results

This step is critical, make sure to verify your results and correct as needed.

Check words exists in other column using MATCH

You can find occurrences of a value in a range using the MATCH function.

=if(isna(match(C3,$A$3:$A$10)), "-","+")

This will render a similar result to the method outlined above.

Explanation

  • Also here we use the IF function.
  • MATCH finds occurrences of a value in a list. If not found it returns an #NA error.
  • If an NA error is returned by our formula (a match is not found) our formula returns a – (minus) sign otherwise a + (plus) sign is written.