I want to conditional format the columns B to G, if one of them contains the text in say, cell H5. This is the formula I use for that, =$H$5=B1 at the moment.

What I want is for the conditional format to work even if its a partial match. E.g. if I search for 'eggs', and a cell has the text 'boiled eggs' that should get highlighted too. I've used the match function before in this manner =MATCH("*text*",G1,0)' so it searches for partial matches. And what I want is for it to conditional format if the text in H5 is a complete match or even a partial match.
So the formula would be something like =match("*H5*"...)

Basically its like the cell is a Google search bar.

Pls see the Inventory tab. https://docs.google.com/spreadsheets/d/1rlATwRPTkofEg0rB5-GSwwBpQDa7ZOoeNvhs0xPOsKA/edit#gid=0

This the formula I use at the moment for exact matches enter image description here


Please try the following formula in Custom formula is


enter image description here

  • No sorry, that's not what I want actually. I want to enter the name into a cell, & conditional format picks up that value and runs to formula to highlight the cells. So its like in place of the eggs, I want the cell reference, so it picks up whichever text is in the cell & matches, partial or full match. The cell should act like a search bar.
    – Genny
    Nov 1 '20 at 10:14
  • Aha... I misunderstood your question. Please check the updated answer. Nov 1 '20 at 12:38
  • 1
    A very useful and clever conditional formatting case. Well thought. To take it a step further. Cell H5 could be a drop down with your ingredients through data validation. Nov 1 '20 at 12:39
  • 1
    Thank you so much AGAIN! Now I'll be googling to breakdown you solution to find out how it works :D
    – Genny
    Nov 1 '20 at 14:09
  • I'll trade my secrets for recipes. Deal? :) Nov 1 '20 at 14:30

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.