3

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

Improve this question

1 Answer 1

Reset to default
2

Please try the following formula in Custom formula is

=IFERROR(IF(MATCH("*"&INDIRECT("H5")&"*",B2,0)>0,true,),false)

enter image description here

Improve this answer
6
  • 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
    Commented Nov 1, 2020 at 10:14
  • Aha... I misunderstood your question. Please check the updated answer.
    – marikamitsos
    Commented Nov 1, 2020 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.
    – marikamitsos
    Commented Nov 1, 2020 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
    Commented Nov 1, 2020 at 14:09
  • I'll trade my secrets for recipes. Deal? :)
    – marikamitsos
    Commented Nov 1, 2020 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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