Need to compare two columns, highlighting true matches

[tenbusi]

Hi again,

I have two sets of data and where i need to use the array in column V2:V154 to find matching values in column O2:O386.

If an exact match is found, i'd like to highlight that cell. The end goal is to highlight in V, all exact matches of column O, so i can then sort by what matched and what didn't match.

I tried to use conditional formatting, new rule, =MATCH($V$2:$V$154,$O$2:$O$386,0) but i noticed that if '4' was in column O, it was highlighting any instance of '4' in column V, eg., 144, 154, etc., when i'd need just the '4'.

I'm sure i'm just missing something silly.

Thanks as always!

 

[JoeMo]

Did you select all the col O cells you want to compare to the values in col V BEFORE you entered the CF rule. If yes, I don't know what else to tell you as the procedure in post #4 works fine for me.

EDIT: in what cell or cells is 320? Is that the only value or is it part of a larger entry (like 320_123)? The formula will only return TRUE if there is an EXACT match.

 

[tenbusi]

Here is a stripped file for reference

https://www.dropbox.com/s/tf4d41aa5y0iyej/Mr Excel.xlsx?dl=0

 

[JoeMo]

Sorry, I don't download from the web. Can you answer the questions from post #11 ?

 

[tenbusi]

I selected the entire column O, then selected conditional formatting, , new rule, Use formula to determine which cell to format, entered exactly as you typed it (though updated the columns for the stripped version to A/B), entered my cell color, and hit ok, then apply.

The match criteria is not part of a larger number, it is an exact match.

O3 = 320
V105 = 320

Exact matches, confirmed via =EXACT(O3,V105)

O3 is not highlighting even though it is found in V105.

 

[Ingolf]

I selected the entire column O, then selected conditional formatting, , new rule, Use formula to determine which cell to format, entered exactly as you typed it (though updated the columns for the stripped version to A/B), entered my cell color, and hit ok, then apply.

The match criteria is not part of a larger number, it is an exact match.

O3 = 320
V105 = 320

Exact matches, confirmed via =EXACT(O3,V105)

O3 is not highlighting even though it is found in V105.

Hi,

That is because you have many duplicate number in column A. In file you attached.
Match function find first occurrence.

 

[tenbusi]

Very much appreciate the help. It's looking like i'll just have to manually troubleshoot these couple hundred like a luddite. Don't spend any more time on this one. THANKS AGAIN!

 

[Ingolf]

You need to highlight all occurrence from column B, found in column A?

 

[tenbusi]

Correct.

B is my search criteria, and A is what i need highlighted if true/match exactly.

 

[JoeMo]

Hi,

That is because you have many duplicate number in column A. In file you attached.
Match function find first occurrence.

That's not correct. if there is ANY occurrence in col V ($V$2:$V$154) of the value in O3, then O3 should be highlighted. Knowing that the value in O3 matches exactly the value in V105 means there is at least one match, so O3 should be highlighted even if the value in V105 is duplicated in an earlier cell in col V that is within the range $V$2:$V$154.