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]

First select all the V col cells in the array you want to highlight if a match is found. Then use this rule:

=ISNUMBER(MATCH($V$2:$V$154,$O$2:$O$386,0))

 

[tenbusi]

i mis-typed above, apologies.

I need to highlight the cells in O using V as my search criteria. I tried just flipping your formula, which seemed to work, but i ended up with a 100% match, which i know to not be true. It looks like it's matching '39' to numbers like '339'.

 

[JoeMo]

Remove the existing CF cells. Then select all col O cells you want to CF.

Then New>UseFormula and use this rule:

=ISNUMBER(MATCH($O2,$V$2:$V$154,0))

 

[tenbusi]

I don't know what 'CF' means.

So i just erased the old rule and tried the new one. It appears to have been much more selective in matching, but in doing a couple cross checks, i'm not seeing it as accurate.

For example, my value in O3 did not highlight but is present in V105.

 

[JoeMo]

if you enter the formula below in any empty cell, what does it return?

=EXACT(O3,V105)

 

[tenbusi]

True

 

[tenbusi]

Here's a snip of it non highlighted - Editing because it didn't transfer the cell font color.

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]SN[/TD]
[/TR]
[TR]
[TD="align: right"]790[/TD]
[/TR]
[TR]
[TD="align: right"]320[/TD]
[/TR]
[TR]
[TD="align: right"]283[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

If that's returning TRUE and O3 is not highlighted, then you haven't applied the CF (Conditional Formatting) correctly. Go back to post #4 and follow the procedure there after you remove the existing CF. Be sure to copy the CF formula directly from your browser and paste it into the CF formula rule window.

 

[tenbusi]

I just tried again and got the same result. I then decided to take the two columns, paste them on their own sheet so i can trouble shoot, copy and pasted exactly, and got the same result.


From the original sheet: =ISNUMBER(MATCH($O2,$V$2:$V$154,0))

From the stripped sheet: =ISNUMBER(MATCH($A2,$B$2:$B$154,0))


It's still not finding 320.