Match help

[jessejones68]

I have cells in Column O that has a list of important companies. I have a list in column B that has a few hundred companies. I want to make the companies that are in column B highlight if they match an important company from column O. I pasted the data into column O so I can move it around anywhere or make it a list if need be its only about 25 companies. O8:O34 B8:B263

The column B data has duplicates in it and also has some blanks, Column O does not have any duplicates in it. As an added bonus the names in column B are not an exact match as the names in column O. Column B has a contract number after the name of the company that column O does not.

If there is a way to highlight the whole row when there is a match found that would be ideal but just the cell in column B would suffice.

Thank you for the help

 

[oldbrewer]

if names are not an exact match, you must define the rules for an acceptable match.

I had a similar situation at work, so I sorted the list in name order then manually corrected typos etc

 

[jessejones68]

if the contract code is in () is there a way to separate them into another cell kind of like changing first name last name = last name, first name?

 

[FDibbins]

1st, as pointed out, if not an exact match, we will need to make some sort of rule to test for "approximation".
2. Can you give a representative (smallish, and anonymous) sample of what you are working with? both lists please.

I would probably use some variation on MATCH or countif

 

[FDibbins]

if the contract code is in () is there a way to separate them into another cell kind of like changing first name last name = last name, first name?

Probably, yes, but again, can we see some sample data?

 

[jessejones68]

B O
CAMP (1B34J) CAMP
UNIVERSAL LLC (90887) Jonestown
PLAIN UTL (G7655) Universal llc
CRANE (F564H) PLAIN UTL

 

[jessejones68]

B CAMP (1B34J) UNIVERSAL LLC UNIVERSAL LLC CRANE (F564H)
O CAMP Jonestown Universal LLC PLAIN UTL

 

[FDibbins]

So is there more than 1 code in a cell?

 

[Aladin Akyurek]

In conditional formatting try the following formula:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($O$8:$O$34,$B8)))