Scenario is a workbook with two sheets. I need a way for cells on sheet 2 to search sheet 1 and highlight themselves if they find any text on sheet 1 that matches.

So cell sheet2!a2 with value of apple searches sheet1. It finds apple somewhere on sheet1 and now sheet2!a2 is highlighted green. Keep in mind, apple may only be part of the cells in sheet1.

Running excel 2012. Any ideas?

The problem with a conditional format that I can setup, even using the countif function, is that the cells have to be completely equal. Meaning if i use the above example. If the cell on sheet2 says "apple" but the cell on sheet1 says"apple-1" the conditional formatting will not work.

How can I adjust the formula to look for the same text within the cell and highlight and not actually look for a exact matching cell?

  • 2
    Sounds like a job for conditional formatting. – Kyle Sep 2 '15 at 19:42
  • haha yup! But I am having a hard time adjusting it to how i need it to. – sferguson Sep 4 '15 at 16:46

Assuming the values are in col A for both sheets, add this conditional formatting rule using a formula to sheet2 col A:

  • Thank you, the issue is though, this type of function requires the cells to be copies of each other. What I am trying to see is if there is a function that will look for the same text in a cell that may not match exactly. So the cell that says "apple" would highlight if it found a cell that said "apple123" – sferguson Sep 4 '15 at 16:45
  • 1
    =COUNTIF(Sheet1!A1,"*"&A1&"*") – Kyle Sep 4 '15 at 16:50
  • Kyle's solution is correct. Sorry, I stopped reading at "any ideas". Wildcards like * and ? (single character) are accepted in Excel formulas. – StartupSpreadsheets Sep 8 '15 at 2:25
  • Learn something new everyday. I swear, all the web searching in the world and I couldn't find something so simple. Thanks all! – sferguson Sep 20 '15 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.