Excel Conditional Formatting Not Accurately Formatting

[rekent]

I am attempting to highlight cells in Column A if the same data contained in the cell is found anywhere in Column B. I am doing this via Conditional Formatting and the formula =NOT(ISNA(VLOOKUP(A1,$B:$B,1,FALSE))). However, multiple cells in Column A that have the same content as a cell in Column B are not highlighting. For one specific example, see B83 and A103, which both include the value 3300-51300, but A103 is not highlighted.

test file.xlsx
	A	B
1	3300-42100	4580-00100
2	3300-22900	4300-66000
3	3343-00301	3300-06000
4	3326-02201	3300-05100
5	3326-00800	3328-03300
6	3328-01701	3328-03401
7	4638-00101	4581-00101
8	3328-01700	4470-00103
9	3328-01800	4599-01100
10	3328-01901	4502-25400
11	3328-01900	4194-94502
12	3300-53600	4300-71500
13	3343-22500	3300-14200
14	3343-06001	3300-11700
15	3343-00101	3300-12500
16	3343-39700	3300-11600
17	3343-48100	4168-02901
18	3328-01201	4214-44905
19	3328-01200	3328-02901
20	3328-01400	3326-03000
21	3300-58100	3300-17300
22	3343-23000	3328-01004
23	3343-50200	4300-57801
24	3343-39400	3300-19700
25	3326-05000	3300-19200
26	3343-06101	3300-19800
27	3328-00601	3300-19300
28	3343-22600	3300-20600
29	3328-03000	3300-19600
30	3328-00600	3300-20500
31	4214-54807	4214-59609
32	3343-48900	3300-23000
33	3343-50700	3328-00503
34	3300-45900	4470-00300
35	3300-46000	3300-25900
36	3343-60000	3328-01602
37	3326-06100	4470-00301
38	3328-00200	3300-27400
39	3343-38500	3300-29400
40	3300-39200	3300-31200
41	3300-11600	3300-31300
42	3326-00500	3300-32200
43	3326-00600	3300-33800
44	3300-59000	3300-32400
45	3326-01400	3300-32600
46	3328-00101	3300-32700
47	3328-00401	3300-32500
48	3328-00501	3300-35100
49	3328-00100	3300-35700
50	3328-00400	3300-35900
51	3328-00801	3300-36000
52	3328-00500	3300-32800
53	3343-51100	3300-37700
54	3343-50800	3300-37800
55	3326-00900	3300-37900
56	3326-01000	3300-38000
57	3326-03500	3300-38100
58	3328-01004	3300-39200
59	3343-51200	3300-39700
60	4214-62237	3300-41700
61	3326-03300	3300-39100
62	3326-03400	3326-08000
63	3343-51500	3326-08300
64	4819-00100	3300-41900
65	4470-00103	3300-41100
66	3300-69600	3300-43000
67	3300-31700	3300-43300
68	3300-72000 NR	3300-43200
69	3300-19200	3300-43400
70	3300-38000	3300-43500
71	3300-18300	3326-09200
72	3300-31200	4300-85500
73	3300-31600	4300-85400
74	3326-07000	4300-85600
75	3300-38800	4300-85800
76	3300-18900	3326-10600
77	3300-38100	3300-48600
78	3343-45400	3326-10900
79	3343-39500	3326-10200
80	4780-01301	3300-50500
81	3343-05400	3300-50900
82	3343-32000	3300-51300
83	4780-01300	4752-00800
84	3300-23000	3300-53400
85	3300-18400	4780-01500
86	4780-01400	3300-53600
87	3300-32400	3300-53700
88	3300-21400	3326-03701
89	3300-45800	3300-55600
90	3300-37900	3300-56100
91	3300-45600	3300-56500
92	3300-45700	4780-00202
93	3300-50500	4786-01600
94	3300-50900	4780-01700
95	3300-69900	3300-56200
96	3300-42300	3300-58100
97	3300-37700	3343-45500
98	3300-39100	3300-57400
99	3300-45400	3300-59000
100	3300-48600	3300-59500
101	3300-50700	3300-60500
102	3300-51300	3343-51100
103	3300-72600	3343-51500
104	3300-45100 NR	3343-51600
105	3343-57900	3300-63200
106	3343-03601	3300-64800
107	4580-00100	3300-64900
108	3300-74300 NR	4214-00000
109	4581-00100	4214-59623
110	3328-03401	3343-52600
111	3326-04500	3343-52700
112	3328-03701	3300-63400
113	3300-33900	3343-52800
114	4780-02001	3300-63300
115	4780-02002	3343-53500
116	4780-02004	3343-53600
117	4780-02006	3300-63700
118	4780-02000	3300-68200
119	4780-02003	3300-67300
120	3300-41100	3300-64400
121	3300-41900	3300-68100
122	3300-42800	3300-64300
123	3300-34100	4768-00200
124	3300-22700	3300-70000
125	4214-20704	3300-68000
126	3300-19300	4780-01301
127	3343-58100	4780-01401
128	3300-32300	3347-00105
129	3300-63300	3347-00110
130	3300-63700	3300-67000
131	3326-02200	4780-01501
132	3328-00503	3300-70400
133	4780-01500	3343-59300
134	3300-64800	3300-67100
135	4780-01501	3343-60400
136	3328-01601	3300-68600
137	3328-01600	3300-70600
138	3300-20500	3300-71700
139	3326-02100	3300-72900
140	3300-41700	3300-74300
141	3326-10200	3300-74400
142	3343-40000	3300-70800
143	3328-03501
144	3326-02400
145	3300-45400 NR
146	3347-00110
147	3328-03500
148	4470-00300
149	4470-00200
150	4470-00301
151	4214-59612
152	3326-00201
153	3326-00202
154	3326-00301
155	3326-00302
156	3300-42200
157	3300-43000
158	3300-43200
159	3300-43300
160	3300-63200
161	3328-00502
162	3343-00901
163	3300-25900
164	3300-56200
165	3326-05300
166	4214-69501
167	3328-02901
168	3300-26500
169	4214-60910
170	3300-25800
171	3326-00200
172	3326-00300
173	3326-03000
174	3328-02902
175	3300-24900
176	3300-11700
177	3343-02401
178	3328-02900
179	3343-26800
180	3300-55600
181	3300-56100
182	3343-07501
183	3300-35000
184	3326-08200
185	4780-00101
186	4780-00102
187	4780-00104
188	3328-02001
189	3300-45100
190	3328-02000
191	3328-03700
192	3326-05200
193	3328-00201
194	3300-50800
195	3328-02601
196	3328-02801
197	3328-02600
198	3328-02800
199	3300-65500
200	3300-43400
201	3326-02401
202	4214-67500
203	3300-53400
204	3328-01001
205	3300-34900
206	4780-02300
207	4214-62904
208	3328-01101
209	3328-01100
210	3343-57100
211	3300-57400
212	3326-03701
213	3300-21500
214	4214-69500
215	4780-01901
216	3300-72000
217	4780-01900
218	3343-41700
219	3343-62500
220	3343-17301
221	3326-04900
222	3300-39700
223	4780-00200
224	4780-00202
225	3300-22800
226	3300-32200
227	3300-33800
228	4168-02900
229	4786-01600
230	3300-19800
231	3300-31300
232	3343-42600
233	3300-36000
234	3300-34000
235	3300-71800
236	3343-62900
237	3343-62900 NR
238	3300-35700
239	3300-35800
240	3300-36100
241	4214-44905
242	3343-58200
243	3300-62300
244	3343-59400
245	3300-35900
246	3300-14200
247	3300-53700
248	3343-45500
249	3328-02300
250	3328-02301
251	3328-02501
252	3328-01501
253	3328-02500
254	3300-69400
255	3326-03200
256	3326-04700
257	3300-69200
258	3328-02201
259	3300-72100
260	4597-00100
261	3300-52600
262	3300-72100 NR
263	3326-09200
264	3347-00103
265	3347-00104
266	3347-00105
267	3347-00106
268	3347-00107
269	3347-00109
270	4780-01804
271	4780-01805
272	3328-02200
273	4780-01802
274	4780-01803
275	3300-32800
276	3328-02002
277	3343-10301
278	3326-03001
279	3326-03401
280	3326-03501
281	3326-03901
282	3300-18600
283	3328-01300
284	3326-08300
285	3300-18500
286	3300-27300
287	3328-01500
288	3326-10900
289	3300-58300
290	3300-39000
291	4581-00101
292	3343-30200
293	3300-29400
294	3300-62100
295	4780-01800
296	3343-54300
297	3326-03900
298	3343-53700
299	3300-25400
300	3328-00702
301	3328-00700
302	3300-08600
303	3328-00703
304	4100-04203
305	3343-54200
306	3343-37800
307	3343-37600
308	3343-53100
309	3343-53200
310	3300-60500
311	3300-64900
312	4214-59623
313	3343-41900
314	3343-43500
315	3300-37800
316	3326-01500
317	3328-03100
318	3328-00802
319	3300-52700
320	3300-59500
321	3300-37100
322	4470-00104
323	4470-00105
324	3300-06000
325	4780-01700
326	3300-56500
327	3300-35100
328	3326-08000
329	3343-37400
330	3328-00701
331	3343-01701
332	3343-08901
333	3300-38400
334	3328-00803
335	3328-01002
336	3328-03101
337	3300-27400
338	3343-52900
339	3343-53800
340	3343-53900
341	3343-54000
342	3328-03102
343	3328-03103
344	3328-01003
345	3328-00800
346	3328-01000
347	3300-42700
348	3343-36800
349	3328-03302
350	3328-03301
351	3300-26200
352	3328-03300
353	4786-01601
354	4786-01602
355	3300-17300
356	3328-00301
357	3328-03402
358	3343-44700
359	3326-10600
360	4780-00100
361	3343-30400
362	4780-01801
363	4780-00201
364	3328-00300
365	3343-41600
366	4599-01100
367	4780-02100
368	4780-02200
369	3300-74400 NR
370	3328-03400
371	3326-03700
372	3343-44500
Patents

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A1048574	Expression	=NOT(ISNA(VLOOKUP(A2,$B:$B,1,FALSE)))	text	NO

 

[Fluff]

The formula must look at the first row in the applies to range.
So either Change A2 to A1 or change the applies to range to start at row2

 

[jtakw]

Hi,

Aside from matching the Start cell in the formula, why not use Just COUNTIF ?

Book3.xlsx
	A	B
1	3300-42100	4580-00100
2	3300-22900	4300-66000
3	3343-00301	3300-06000
4	3326-02201	3300-05100
5	3326-00800	3328-03300
6	3328-01701	3328-03401
7	4638-00101	4581-00101
8	3328-01700	4470-00103
9	3328-01800	4599-01100
10	3328-01901	4502-25400
11	3328-01900	4194-94502
12	3300-53600	4300-71500
13	3343-22500	3300-14200
14	3343-06001	3300-11700
15	3343-00101	3300-12500
16	3343-39700	3300-11600
17	3343-48100	4168-02901
18	3328-01201	4214-44905
19	3328-01200	3328-02901
20	3328-01400	3326-03000
21	3300-58100	3300-17300
22	3343-23000	3328-01004
23	3343-50200	4300-57801
24	3343-39400	3300-19700
25	3326-05000	3300-19200
26	3343-06101	3300-19800
27	3328-00601	3300-19300
28	3343-22600	3300-20600
29	3328-03000	3300-19600
30	3328-00600	3300-20500
31	4214-54807	4214-59609
32	3343-48900	3300-23000
33	3343-50700	3328-00503
34	3300-45900	4470-00300
35	3300-46000	3300-25900
36	3343-60000	3328-01602
37	3326-06100	4470-00301
38	3328-00200	3300-27400
39	3343-38500	3300-29400
40	3300-39200	3300-31200
41	3300-11600	3300-31300
42	3326-00500	3300-32200
43	3326-00600	3300-33800
44	3300-59000	3300-32400
45	3326-01400	3300-32600
46	3328-00101	3300-32700
47	3328-00401	3300-32500
48	3328-00501	3300-35100
49	3328-00100	3300-35700
50	3328-00400	3300-35900
51	3328-00801	3300-36000
52	3328-00500	3300-32800
53	3343-51100	3300-37700
54	3343-50800	3300-37800
55	3326-00900	3300-37900
56	3326-01000	3300-38000
57	3326-03500	3300-38100
58	3328-01004	3300-39200
59	3343-51200	3300-39700
60	4214-62237	3300-41700
61	3326-03300	3300-39100
62	3326-03400	3326-08000
63	3343-51500	3326-08300
64	4819-00100	3300-41900
65	4470-00103	3300-41100
66	3300-69600	3300-43000
67	3300-31700	3300-43300
68	3300-72000 NR	3300-43200
69	3300-19200	3300-43400
70	3300-38000	3300-43500
71	3300-18300	3326-09200
72	3300-31200	4300-85500
73	3300-31600	4300-85400
74	3326-07000	4300-85600
75	3300-38800	4300-85800
76	3300-18900	3326-10600
77	3300-38100	3300-48600
78	3343-45400	3326-10900
79	3343-39500	3326-10200
80	4780-01301	3300-50500
81	3343-05400	3300-50900
82	3343-32000	3300-51300
83	4780-01300	4752-00800
84	3300-23000	3300-53400
85	3300-18400	4780-01500
86	4780-01400	3300-53600
87	3300-32400	3300-53700
88	3300-21400	3326-03701
89	3300-45800	3300-55600
90	3300-37900	3300-56100
91	3300-45600	3300-56500
92	3300-45700	4780-00202
93	3300-50500	4786-01600
94	3300-50900	4780-01700
95	3300-69900	3300-56200
96	3300-42300	3300-58100
97	3300-37700	3343-45500
98	3300-39100	3300-57400
99	3300-45400	3300-59000
100	3300-48600	3300-59500
101	3300-50700	3300-60500
102	3300-51300	3343-51100
103	3300-72600	3343-51500
104	3300-45100 NR	3343-51600
105	3343-57900	3300-63200
106	3343-03601	3300-64800
107	4580-00100	3300-64900
108	3300-74300 NR	4214-00000
109	4581-00100	4214-59623
110	3328-03401	3343-52600
111	3326-04500	3343-52700
112	3328-03701	3300-63400
113	3300-33900	3343-52800
114	4780-02001	3300-63300
115	4780-02002	3343-53500
116	4780-02004	3343-53600
117	4780-02006	3300-63700
118	4780-02000	3300-68200
119	4780-02003	3300-67300
120	3300-41100	3300-64400
121	3300-41900	3300-68100
122	3300-42800	3300-64300
123	3300-34100	4768-00200
124	3300-22700	3300-70000
125	4214-20704	3300-68000
126	3300-19300	4780-01301
127	3343-58100	4780-01401
128	3300-32300	3347-00105
129	3300-63300	3347-00110
130	3300-63700	3300-67000
131	3326-02200	4780-01501
132	3328-00503	3300-70400
133	4780-01500	3343-59300
134	3300-64800	3300-67100
135	4780-01501	3343-60400
136	3328-01601	3300-68600
137	3328-01600	3300-70600
138	3300-20500	3300-71700
139	3326-02100	3300-72900
140	3300-41700	3300-74300
141	3326-10200	3300-74400
142	3343-40000	3300-70800
143	3328-03501
144	3326-02400
145	3300-45400 NR
146	3347-00110
147	3328-03500
148	4470-00300
149	4470-00200
150	4470-00301
151	4214-59612
152	3326-00201
153	3326-00202
154	3326-00301
155	3326-00302
156	3300-42200
157	3300-43000
158	3300-43200
159	3300-43300
160	3300-63200
161	3328-00502
162	3343-00901
163	3300-25900
164	3300-56200
165	3326-05300
166	4214-69501
167	3328-02901
168	3300-26500
169	4214-60910
170	3300-25800
171	3326-00200
172	3326-00300
173	3326-03000
174	3328-02902
175	3300-24900
176	3300-11700
177	3343-02401
178	3328-02900
179	3343-26800
180	3300-55600
181	3300-56100
182	3343-07501
183	3300-35000
184	3326-08200
185	4780-00101
186	4780-00102
187	4780-00104
188	3328-02001
189	3300-45100
190	3328-02000
191	3328-03700
192	3326-05200
193	3328-00201
194	3300-50800
195	3328-02601
196	3328-02801
197	3328-02600
198	3328-02800
199	3300-65500
200	3300-43400
201	3326-02401
202	4214-67500
203	3300-53400
204	3328-01001
205	3300-34900
206	4780-02300
207	4214-62904
208	3328-01101
209	3328-01100
210	3343-57100
211	3300-57400
212	3326-03701
213	3300-21500
214	4214-69500
215	4780-01901
216	3300-72000
217	4780-01900
218	3343-41700
219	3343-62500
220	3343-17301
221	3326-04900
222	3300-39700
223	4780-00200
224	4780-00202
225	3300-22800
226	3300-32200
227	3300-33800
228	4168-02900
229	4786-01600
230	3300-19800
231	3300-31300
232	3343-42600
233	3300-36000
234	3300-34000
235	3300-71800
236	3343-62900
237	3343-62900 NR
238	3300-35700
239	3300-35800
240	3300-36100
241	4214-44905
242	3343-58200
243	3300-62300
244	3343-59400
245	3300-35900
246	3300-14200
247	3300-53700
248	3343-45500
249	3328-02300
250	3328-02301
251	3328-02501
252	3328-01501
253	3328-02500
254	3300-69400
255	3326-03200
256	3326-04700
257	3300-69200
258	3328-02201
259	3300-72100
260	4597-00100
261	3300-52600
262	3300-72100 NR
263	3326-09200
264	3347-00103
265	3347-00104
266	3347-00105
267	3347-00106
268	3347-00107
269	3347-00109
270	4780-01804
271	4780-01805
272	3328-02200
273	4780-01802
274	4780-01803
275	3300-32800
276	3328-02002
277	3343-10301
278	3326-03001
279	3326-03401
280	3326-03501
281	3326-03901
282	3300-18600
283	3328-01300
284	3326-08300
285	3300-18500
286	3300-27300
287	3328-01500
288	3326-10900
289	3300-58300
290	3300-39000
291	4581-00101
292	3343-30200
293	3300-29400
294	3300-62100
295	4780-01800
296	3343-54300
297	3326-03900
298	3343-53700
299	3300-25400
300	3328-00702
301	3328-00700
302	3300-08600
303	3328-00703
304	4100-04203
305	3343-54200
306	3343-37800
307	3343-37600
308	3343-53100
309	3343-53200
310	3300-60500
311	3300-64900
312	4214-59623
313	3343-41900
314	3343-43500
315	3300-37800
316	3326-01500
317	3328-03100
318	3328-00802
319	3300-52700
320	3300-59500
321	3300-37100
322	4470-00104
323	4470-00105
324	3300-06000
325	4780-01700
326	3300-56500
327	3300-35100
328	3326-08000
329	3343-37400
330	3328-00701
331	3343-01701
332	3343-08901
333	3300-38400
334	3328-00803
335	3328-01002
336	3328-03101
337	3300-27400
338	3343-52900
339	3343-53800
340	3343-53900
341	3343-54000
342	3328-03102
343	3328-03103
344	3328-01003
345	3328-00800
346	3328-01000
347	3300-42700
348	3343-36800
349	3328-03302
350	3328-03301
351	3300-26200
352	3328-03300
353	4786-01601
354	4786-01602
355	3300-17300
356	3328-00301
357	3328-03402
358	3343-44700
359	3326-10600
360	4780-00100
361	3343-30400
362	4780-01801
363	4780-00201
364	3328-00300
365	3343-41600
366	4599-01100
367	4780-02100
368	4780-02200
369	3300-74400 NR
370	3328-03400
371	3326-03700
372	3343-44500
Sheet1083

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A372	Expression	=COUNTIF(B:B,A1)	text	NO

 

[rekent]

The formula must look at the first row in the applies to range.
So either Change A2 to A1 or change the applies to range to start at row2

Typo on my part. The equation as shown at the bottom of the XL2BB embedded sheet is starting at A1.

 

[rekent]

Hi,

Aside from matching the Start cell in the formula, why not use Just COUNTIF ?

I just plugged the COUNTIF formula into the actual sheet and got the same results as the prior formula that is missing values, the 3300-51300 example specifically. Yours shows that highlighted though, so now I am further confused.

 

[jtakw]

Actually, it is B82 and A102
In an empty cell on your sheet, enter:

=B82=A102

Is it TRUE ?

 

[KenU]

When you select all or part of column A to apply the CF to, be sure that the active cell (A1 using your test data as I copied it) is what appears in the CF formula as first argument for MATCH. Then try this CF formula.

=ISNUMBER(MATCH(A1,$B:$B,0))

By the way, VLOOKUP may give unexpected results when the range is not sorted.

Hope that helps.

Regards,
Ken

 

[rekent]

Thanks @KenU, that one did it.

 

[Fluff]

You don't actually need the isnumber & can just use.

=MATCH(A1,$B:$B,0)