Find and highlight duplicate values in multiple sheets

SM extract 8.21.2013 Cust-sample.xlsx

Hello.

I want to highlight the cells under System Name (col 1) in sheet 1 that are found in Host (col 1) in sheet 2 "OCPtabvHost"

The text isnt in the same format in the 2nd sheet (it is lower case and has additional text). I need to find the duplicate roots

Thanks

for example. BL-ESX1PP is found in col 1 of the first sheet. the equivalent entry is bl-esx1pp.ipmi.tfn.com in the second sheet. There is additional text that for this task is unnecessary. Also the case is different

Originally Posted by L-Drr

And is the root always the part up to the first point? And is it always the same amount of characters?
In other words: how can you tell Excel (and me ) in logical statements what the root of every cell is?

Yes the part that i care about is always the beginning. The length differs. The part at the end that is different and not
important is 13 characters in length.

It says conditional formatting cannot use other worksheets .

That looks like what i want though. But i dont know how to take your formula further.