Conditional Format based on Partial Match to Range?

[Jordan J]

Hello! I need some help with a conditional formatting problem that incorporates partial matches. I've uploaded an example problem as an attachment.

I need the cells in Sheet 1, Column B to be highlighted if they contain data that is a partial match to the named range "Real" on sheet "Animals". The cells that should highlight are marked in Column A with a "Y"

Sheet 1 contains more information in each cell than the range "Real".

I've been able to get complete matches from the range to highlight with =ISNUMBER, and I've been able to get individual partial matches to highlight with =SEARCH, but I need something that will do both: highlight cells that partially match a cell within the range.

Any help would be appreciated.

Thank you
-Jordan J

 

[bosco_yip]

Try,

In "Sheet1", select A1:B7 >> Conditional Formatting >> Use a formula…..



Regards
Bosco

 

[azumi]

Another options

 

[Jordan J]

Try,

In "Sheet1", select A1:B7 >> Conditional Formatting >> Use a formula…..

View attachment 53013

Regards
Bosco

Thank you for responding. I'm not sure this will work- the Y value in column A is only there for me to see whether the highlighting is correct, it would not be present in the actual problem.

 

[Jordan J]

Another options

Thank you for your hard work- This formula passed the initial test (I typed in 3 lion in another cell and it did not highlight, yay!) however it couldn't pass further testing (I typed in 32 lion and it highlighted: it pinged on the "2 lion" within "32 lion", I believe.) How would one go about tying the position of the search query to the right/front characters?