Forum Discussion

Anıl Adaş's avatar
Anıl Adaş
Brass Contributor
Jul 10, 2018
Solved

Find words automatically in an Excel sheet

I have an excel sheet including many words.   Is it possible to see automotically these words including or not in a different excel file.   For example, The words in Sheet 1 are marked automatica...
excel
Formulas and Functions
office 365
  • Haytham Amairah's avatar
    Haytham Amairah
    Jul 11, 2018

    Hi,

     

    You can do this by modifying the formula this way:

    =AND(ISNUMBER(MATCH("*"&A1&"*",Sheet1!$A:$A,0)),NOT(ISBLANK(A1)))

    So that it will match the word in sheet 2 with the word in sheet 1 even if the word in sheet 2 is included inside any cell of column A in sheet 1.

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jul 10, 2018

Hi,

 

You have to use the Conditional Formatting by using the below formula in https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f.

 

=ISNUMBER(MATCH(A1,Sheet1!$A:$A,0))

 

For an example, please find the attached workbook.

Regards

  • Anıl Adaş's avatar
    Anıl Adaş
    Brass Contributor
    Jul 10, 2018

    Is it possible to use this formula like below? For example; Peugeot Motor Company is written in sheet1 but there is only Peugeot written in sheet2, it can be a formula works at that situation?

     

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Jul 11, 2018

      Hi,

       

      You can do this by modifying the formula this way:

      =AND(ISNUMBER(MATCH("*"&A1&"*",Sheet1!$A:$A,0)),NOT(ISBLANK(A1)))

      So that it will match the word in sheet 2 with the word in sheet 1 even if the word in sheet 2 is included inside any cell of column A in sheet 1.

  • Anıl Adaş's avatar
    Anıl Adaş
    Brass Contributor
    Jul 10, 2018

    Example is very useful for me.

     

    Thanks a lot..

Resources