Formula to compare 2 cells to 2 columns and return yes or no

[willow1985]

I have file numbers in column C and D on a sheet called "CC". I want to see if the file number listed in C or D exists in column A on Sheet 1 or Column C on sheet 2 and return a "Yes" if it exists or a "No" if it is not found. If C or D are blank, return a "No" if possible.

Any help would be greatly appreciated!

Book1
	B	C	D
1	Latest File #	Previous File #	Match Yes/No
2
3
4	102700
5
6
7	68720
8	68721
9
10	68764
11	N/A
12	68781
13	68784
14	68901
15	55283
16	81008
17	81034
18	80893
19	68969
20	105362
21	105627
22	105651
23	N/A
24	80268	78814
25	68904
26	68902
27	68914
28	68915
29	68903
30	69086
31	69087
32	80815
33	105685
34	105688
35	80940
36	80946	68201
37	105978
38	105864
39	105867
40	335020	334888
41	69356
42	81029
43	69385
44	69386
45	69399
46	69470
47	81185
48	69495
49	N/A
50	163181
51	N/A
52	55421
53	68995
54	69667
55	81821
56	46780
57	24202
58	69725
59	69787
60	106682
61	55002	47734
62	81797
63	82152
64	81482
65	335273
66	69863
67	81911
68	82085
69	69877
70	336045
71	70021
72	70039
73	69910
74	70000
75	57282
76	70149
77	104247	104246
78	107178
79	80964
80	82349
81	82418
82	70218
83	70233
84	81794	81793
85	70243
86	70265
87	N/A
88	70367
89	70385
90	70312
91	81155
92	69643
93	82692
94	70487
95	70501
96	70502
97	219016
98	82973
99	83251
100	81906
101	337084
102	70252
103	38297
104	70666
105	107913
106	101923
107	All
108	N/A
109	38297
110	336777
111	N/A
112	107863	107536
113	102037
114
115	70846
CC

Book1
	A
1	File Number
2	213172
3	213171
4	213170
5	213169
6	213168
7	213167
8	213166
9	213165
10	213164
11	213163
12	213162
13	213161
14	213160
15	213159
16	213158
17	213157
18	213156
19	213155
20	213154
21	213153
22	213152
23	213151
24	213150
25	213149
26	213148
27	213147
28	213146
29	213145
30	213144
31	213143
32	213142
33	213141
34	213140
35	213139
36	213138
37	213137
38	213136
39	213135
40	213134
41	213133
42	213132
43	213131
44	213130
45	213129
46	213128
47	213127
48	213126
49	213125
50	213124
51	213123
52	213122
53	213121
54	213120
55	213119
56	213118
57	213117
58	213116
59	213115
60	213114
61	213113
62	213112
63	213111
64	213110
65	213109
66	213108
67	213107
68	213106
69	213105
70	213104
71	213103
72	213102
73	213101
74	213100
75	213099
76	213098
77	213097
78	213096
79	213095
80	213094
81	213093
82	213092
83	213091
84	213090
85	213089
86	213088
87	213087
88	213086
89	213085
90	213084
91	213083
92	213082
93	213081
94	213080
95	213079
96	213078
97	213077
98	213076
99	213075
100	213074
101	213073
102	213072
103	213071
104	213070
105	213069
106	213068
107	213067
108	213066
109	213065
110	213064
111	213063
112	213062
113	213061
114	213060
115	213059
116	213058
117	213057
118	213056
119	213055
120	213054
121	213053
122	213052
123	213051
124	213050
125	213049
126	213048
127	213047
128	213046
129	213045
130	213044
131	213043
132	213042
133	213041
134	213040
135	213039
136	213038
137	213037
138	213036
139	213035
140	213034
141	213033
142	213032
143	213031
144	213030
145	213029
146	213028
147	213027
148	213026
149	213025
150	213024
151	213023
152	213022
153	213021
154	213020
155	213019
156	213018
157	213017
158	213016
159	213015
160	213014
161	213013
162	213012
163	213011
164	213010
165	213009
166	213008
167	213007
168	213006
169	213005
170	213004
171	213003
172	213002
173	213001
174	213000
175	212999
176	212998
177	212997
178	212996
179	212995
180	212994
181	212993
182	212992
183	212991
184	212990
185	212989
186	212988
187	212987
188	212986
189	212985
190	212984
191	212983
192	212982
193	212981
194	212980
195	212979
196	212978
197	212977
198	212976
199	212975
200	212974
201	212973
Sheet1

Book1
	C
1	File Number
2	42244
3	42243
4	42195
5	42205
6	42245
7	42248
8	42249
9	42237
10	42250
11	42251
12	42159
13	42252
14	42253
15	42239
16	42246
17	42226
18	42227
19	42232
20	42234
21	42240
22	42241
23	42242
24	42247
25	42223
26	42233
27	42235
28	42236
29	42228
30	42229
31	42230
32	42231
33	42238
34	42221
35	42224
36	42225
37	41536
38	42215
39	42217
40	42220
41	42222
42	42175
43	42176
44	42177
45	42178
46	42179
47	42180
48	42189
49	42198
50	42214
51	42216
52	42218
53	42219
54	42194
55	42196
56	42197
57	42201
58	42206
59	42207
60	42209
61	42210
62	42211
63	42212
64	42213
65	42208
66	42069
67	42191
68	42192
69	42193
70	42199
71	42200
72	42202
73	42204
74	42183
75	42188
76	42173
77	42187
78	42190
79	42166
80	42184
81	42185
82	42186
83	42130
84	42168
85	42181
86	42182
87	42161
88	42164
89	42167
90	42170
91	42172
92	42174
93	42157
94	42171
95	42158
96	42160
97	42162
98	42163
99	42169
100	42150
101	42152
102	42153
103	42139
104	42141
105	42142
106	42143
107	42147
108	42148
109	42149
110	42154
111	42155
112	42156
113	41478
114	42138
115	42144
116	42145
117	42151
118	42131
119	42132
120	42133
121	42137
122	42140
123	42146
124	42120
125	42121
126	42123
127	42124
128	42127
129	42128
130	42129
131	42134
132	42135
133	42136
134	42079
135	42108
136	42117
137	42118
138	42122
139	42125
140	42126
141	40928
142	42113
143	42116
144	42119
145	42104
146	42109
147	42110
148	42111
149	42112
150	42114
151	42115
152	42080
153	42081
154	42103
155	42105
156	42106
157	42107
158	42090
159	42102
160	42097
161	42098
162	42064
163	42065
164	42092
165	42093
166	42099
167	42101
168	42066
169	42067
170	42068
171	42082
172	42083
173	42085
174	42087
175	42094
176	42095
177	42075
178	42076
179	42078
180	42084
181	42086
182	42089
183	42096
184	42100
185	42070
186	42074
187	42077
188	42091
189	42063
190	42071
191	42088
192	42050
193	42060
194	42062
195	42072
196	42073
197	42051
198	42055
199	42056
200	42057
201	42058
202	42061
203	42031
204	42047
205	42059
206	42038
207	42043
208	42044
209	42045
210	42046
211	42048
212	42049
213	42053
214	42025
215	42034
216	42035
217	42039
218	42040
219	42054
220	42013
221	42032
222	42033
223	42041
224	42009
225	42012
226	42016
227	42020
228	42029
229	42036
230	42037
231	42042
232	42019
233	42021
234	42022
235	42023
236	42024
237	42026
238	42027
239	42028
240	42030
241	42011
242	42018
243	42004
244	42005
245	42010
246	42014
247	42015
248	41988
249	41989
250	42017
251	42000
252	42007
253	41849
254	41965
255	42001
256	42006
257	42008
258	41990
259	41991
260	41994
261	41995
262	41998
263	42002
264	42003
265	41979
266	41984
267	41986
268	41992
269	41996
270	41997
271	41999
272	41974
273	41977
274	41985
275	41987
276	41993
277	41972
278	41973
279	41981
280	41982
281	41960
282	41975
283	41976
284	41978
285	41980
286	41983
287	41956
288	41957
289	41959
290	41961
291	41962
292	41966
293	41967
294	41968
295	41969
296	41970
297	41971
298	41951
299	41953
300	41954
301	41955
302	41958
303	41963
304	41964
305	41937
306	41947
307	41950
308	41943
309	41949
310	41940
311	41945
312	41946
313	41952
314	41925
315	41928
316	41931
317	41933
318	41939
319	41941
320	41942
321	41944
322	41948
323	41910
324	41911
325	41915
326	41916
327	41917
328	41918
329	41930
330	41932
331	41934
332	41935
333	41936
334	41866
335	41919
336	41920
337	41921
338	41922
339	41924
340	41926
341	41927
342	41929
343	41938
344	41923
345	41517
346	41893
347	41894
348	41895
349	41896
350	41903
351	41904
352	41905
353	41906
354	41907
355	41909
356	41912
357	41913
358	41864
359	41890
360	41892
361	41897
362	41898
363	41899
364	41900
365	41901
366	41902
367	41914
368	41876
369	41888
370	41908
371	41859
372	41886
373	41887
374	41889
375	41891
376	41479
377	41862
378	41871
379	41873
380	41874
381	41875
382	41878
383	41879
384	41880
385	41881
386	41882
387	41883
388	41884
389	41885
390	41518
391	41855
392	41860
393	41861
394	41867
395	41868
396	41869
397	41872
398	41877
399	41829
400	41853
401	41854
402	41856
403	41858
404	41863
405	41865
406	41870
407	41537
408	41841
409	41842
410	41843
411	41851
412	41857
413	41780
414	41810
415	41840
416	41844
417	41845
418	41846
419	41847
420	41848
421	41850
422	41852
423	41773
424	41826
425	41830
426	41833
427	41834
428	41836
429	41837
430	41838
431	41788
432	41821
433	41823
434	41827
435	41828
436	41835
437	41839
438	41824
439	41825
440	41831
441	41832
442	41801
443	41809
444	41811
445	41814
446	41815
447	41817
448	41818
449	41820
450	41763
451	41805
452	41803
453	41804
454	41806
455	41807
456	41808
457	41813
458	41816
459	41819
460	41822
461	41776
462	41792
463	41793
464	41764
465	41786
466	41791
467	41794
468	41796
469	41797
470	41799
471	41812
472	41790
473	41795
474	41800
475	41762
476	41777
477	41784
478	41785
479	41789
480	41802
481	41735
482	41775
Sheet2

 

[etaf]

IGNORE
Not sure now what I'm matching

IF Sheet CC - C or D
Matches
Sheet1 A or C

i have used
=IF(AND(A2="",B2=""),"",IF(COUNTIFS(Sheet1!A:C,CC!A2)>0,"YES",IF(COUNTIFS(Sheet1!A:C,CC!B2)>0,"YES","NO")))

What happens if both numbers in A & B match

Nothing matches in your example

 

[willow1985]

IGNORE
Not sure now what I'm matching

IF Sheet CC - C or D
Matches
Sheet1 A or C

i have used
=IF(AND(A2="",B2=""),"",IF(COUNTIFS(Sheet1!A:C,CC!A2)>0,"YES",IF(COUNTIFS(Sheet1!A:C,CC!B2)>0,"YES","NO")))

What happens if both numbers in A & B match

Nothing matches in your example

I could not include all of the data and I don't see how your formula would work...
It is only referencing Sheet 1 to look for matches, not sheet 2 as well and It is using A2 and B2 as a reference in sheet CC??? Shouldn't it be B2 and C2??

there should be matches with the complete data set but it is over 3000 lines, with this formula I am getting no matches.

 

[etaf]

Yes sorry
The count is checking to see if the number exists
What happens if the Number in A2 matches both Sheet 1 or sheet 2

 

[etaf]

IN Sheet CC - column E
=IF(AND(C2="",D2=""),"",IF(OR(COUNTIFS(Sheet1!A:A,CC!C2)>0,COUNTIFS(Sheet2!C:C,CC!C2)>0 ),"YES",IF(OR(COUNTIFS(Sheet1!A:A,CC!D2)>0,COUNTIFS(Sheet2!C:C,CC!D2)>0 ), "YES","NO")))

This is Checking to see if the Number in Sheet CC in C2 matches either Sheet1 column A or Sheet 2 column C
Using
OR(COUNTIFS(Sheet1!A:A,CC!C2)>0,COUNTIFS(Sheet2!C:C,CC!C2)>0 )

Then same for column D
OR(COUNTIFS(Sheet1!A:A,CC!D2)>0,COUNTIFS(Sheet2!C:C,CC!D2)>0 )

then added to a NESTED IF to return a YES or a NO

as its across multiple sheets
I thought rather than XL2BB
I would add to dropbox

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[jtakw]

Hi,

Is this what you mean?

Assumes CC sheet Columns A & B blanks are True Blanks, Not formula blanks.
Adjust Column/Cell ranges for Sheet1 and Sheet2 in formula to suit your data.

Book1
	B	C	D
1	Latest File #	Previous File #	Match Yes/No
2	213172		Yes
3		42244	Yes
4	102700		No
5			No
6			No
7	68720		No
8	68721		No
9			No
10	68764		No
11	N/A		No
12	68781		No
13	68784		No
14	68901		No
15	55283		No
16	81008		No
17	81034		No
18	80893		No
19	68969		No
20	105362		No
21	105627		No
22	105651		No
23	N/A		No
24	80268	78814	No
25	68904		No
26	68902		No
27	68914		No
28	68915		No
29	68903		No
30	69086		No
31	69087		No
32	80815		No
33	105685		No
34	105688		No
35	80940		No
36	80946	68201	No
37	105978		No
38	105864		No
39	105867		No
40	335020	334888	No
41	69356		No
42	81029		No
43	69385		No
44	69386		No
45	69399		No
46	69470		No
47	81185		No
48	69495		No
49	N/A		No
50	163181		No
51	N/A		No
52	55421		No
53	68995		No
54	69667		No
55	81821		No
56	46780		No
57	24202		No
58	69725		No
59	69787		No
60	106682		No
61	55002	47734	No
62	81797		No
63	82152		No
64	81482		No
65	335273		No
66	69863		No
67	81911		No
68	82085		No
69	69877		No
70	336045		No
71	70021		No
72	70039		No
73	69910		No
74	70000		No
75	57282		No
76	70149		No
77	104247	104246	No
78	107178		No
79	80964		No
80	82349		No
81	82418		No
82	70218		No
83	70233		No
84	81794	81793	No
85	70243		No
86	70265		No
87	N/A		No
88	70367		No
89	70385		No
90	70312		No
91	81155		No
92	69643		No
93	82692		No
94	70487		No
95	70501		No
96	70502		No
97	219016		No
98	82973		No
99	83251		No
100	81906		No
101	337084		No
102	70252		No
103	38297		No
104	70666		No
105	107913		No
106	101923		No
107	All		No
108	N/A		No
109	38297		No
110	336777		No
111	N/A		No
112	107863	107536	No
113	102037		No
114			No
115	70846		No
CC

Cell Formulas
Range	Formula
D2:D115	D2	=IF(SUM(COUNTIF(Sheet1!A$1:A$500,B2),COUNTIF(Sheet1!A$1:A$500,C2),COUNTIF(Sheet2!C$1:C$500,B2),COUNTIF(Sheet2!C$1:C$500,C2)),"Yes","No")

Book1
	A
1	File Number
2	213172
3	213171
4	213170
5	213169
6	213168
7	213167
8	213166
9	213165
10	213164
11	213163
12	213162
13	213161
14	213160
15	213159
16	213158
17	213157
18	213156
19	213155
20	213154
21	213153
22	213152
23	213151
24	213150
25	213149
26	213148
27	213147
28	213146
29	213145
30	213144
31	213143
32	213142
33	213141
34	213140
35	213139
36	213138
37	213137
38	213136
39	213135
40	213134
41	213133
42	213132
43	213131
44	213130
45	213129
46	213128
47	213127
48	213126
49	213125
50	213124
51	213123
52	213122
53	213121
54	213120
55	213119
56	213118
57	213117
58	213116
59	213115
60	213114
61	213113
62	213112
63	213111
64	213110
65	213109
66	213108
67	213107
68	213106
69	213105
70	213104
71	213103
72	213102
73	213101
74	213100
75	213099
76	213098
77	213097
78	213096
79	213095
80	213094
81	213093
82	213092
83	213091
84	213090
85	213089
86	213088
87	213087
88	213086
89	213085
90	213084
91	213083
92	213082
93	213081
94	213080
95	213079
96	213078
97	213077
98	213076
99	213075
100	213074
101	213073
102	213072
103	213071
104	213070
105	213069
106	213068
107	213067
108	213066
109	213065
110	213064
111	213063
112	213062
113	213061
114	213060
115	213059
116	213058
117	213057
118	213056
119	213055
120	213054
121	213053
122	213052
123	213051
124	213050
125	213049
126	213048
127	213047
128	213046
129	213045
130	213044
131	213043
132	213042
133	213041
134	213040
135	213039
136	213038
137	213037
138	213036
139	213035
140	213034
141	213033
142	213032
143	213031
144	213030
145	213029
146	213028
147	213027
148	213026
149	213025
150	213024
151	213023
152	213022
153	213021
154	213020
155	213019
156	213018
157	213017
158	213016
159	213015
160	213014
161	213013
162	213012
163	213011
164	213010
165	213009
166	213008
167	213007
168	213006
169	213005
170	213004
171	213003
172	213002
173	213001
174	213000
175	212999
176	212998
177	212997
178	212996
179	212995
180	212994
181	212993
182	212992
183	212991
184	212990
185	212989
186	212988
187	212987
188	212986
189	212985
190	212984
191	212983
192	212982
193	212981
194	212980
195	212979
196	212978
197	212977
198	212976
199	212975
200	212974
201	212973
Sheet1

Book1
	C
1	File Number
2	42244
3	42243
4	42195
5	42205
6	42245
7	42248
8	42249
9	42237
10	42250
11	42251
12	42159
13	42252
14	42253
15	42239
16	42246
17	42226
18	42227
19	42232
20	42234
21	42240
22	42241
23	42242
24	42247
25	42223
26	42233
27	42235
28	42236
29	42228
30	42229
31	42230
32	42231
33	42238
34	42221
35	42224
36	42225
37	41536
38	42215
39	42217
40	42220
41	42222
42	42175
43	42176
44	42177
45	42178
46	42179
47	42180
48	42189
49	42198
50	42214
51	42216
52	42218
53	42219
54	42194
55	42196
56	42197
57	42201
58	42206
59	42207
60	42209
61	42210
62	42211
63	42212
64	42213
65	42208
66	42069
67	42191
68	42192
69	42193
70	42199
71	42200
72	42202
73	42204
74	42183
75	42188
76	42173
77	42187
78	42190
79	42166
80	42184
81	42185
82	42186
83	42130
84	42168
85	42181
86	42182
87	42161
88	42164
89	42167
90	42170
91	42172
92	42174
93	42157
94	42171
95	42158
96	42160
97	42162
98	42163
99	42169
100	42150
101	42152
102	42153
103	42139
104	42141
105	42142
106	42143
107	42147
108	42148
109	42149
110	42154
111	42155
112	42156
113	41478
114	42138
115	42144
116	42145
117	42151
118	42131
119	42132
120	42133
121	42137
122	42140
123	42146
124	42120
125	42121
126	42123
127	42124
128	42127
129	42128
130	42129
131	42134
132	42135
133	42136
134	42079
135	42108
136	42117
137	42118
138	42122
139	42125
140	42126
141	40928
142	42113
143	42116
144	42119
145	42104
146	42109
147	42110
148	42111
149	42112
150	42114
151	42115
152	42080
153	42081
154	42103
155	42105
156	42106
157	42107
158	42090
159	42102
160	42097
161	42098
162	42064
163	42065
164	42092
165	42093
166	42099
167	42101
168	42066
169	42067
170	42068
171	42082
172	42083
173	42085
174	42087
175	42094
176	42095
177	42075
178	42076
179	42078
180	42084
181	42086
182	42089
183	42096
184	42100
185	42070
186	42074
187	42077
188	42091
189	42063
190	42071
191	42088
192	42050
193	42060
194	42062
195	42072
196	42073
197	42051
198	42055
199	42056
200	42057
201	42058
202	42061
203	42031
204	42047
205	42059
206	42038
207	42043
208	42044
209	42045
210	42046
211	42048
212	42049
213	42053
214	42025
215	42034
216	42035
217	42039
218	42040
219	42054
220	42013
221	42032
222	42033
223	42041
224	42009
225	42012
226	42016
227	42020
228	42029
229	42036
230	42037
231	42042
232	42019
233	42021
234	42022
235	42023
236	42024
237	42026
238	42027
239	42028
240	42030
241	42011
242	42018
243	42004
244	42005
245	42010
246	42014
247	42015
248	41988
249	41989
250	42017
251	42000
252	42007
253	41849
254	41965
255	42001
256	42006
257	42008
258	41990
259	41991
260	41994
261	41995
262	41998
263	42002
264	42003
265	41979
266	41984
267	41986
268	41992
269	41996
270	41997
271	41999
272	41974
273	41977
274	41985
275	41987
276	41993
277	41972
278	41973
279	41981
280	41982
281	41960
282	41975
283	41976
284	41978
285	41980
286	41983
287	41956
288	41957
289	41959
290	41961
291	41962
292	41966
293	41967
294	41968
295	41969
296	41970
297	41971
298	41951
299	41953
300	41954
301	41955
302	41958
303	41963
304	41964
305	41937
306	41947
307	41950
308	41943
309	41949
310	41940
311	41945
312	41946
313	41952
314	41925
315	41928
316	41931
317	41933
318	41939
319	41941
320	41942
321	41944
322	41948
323	41910
324	41911
325	41915
326	41916
327	41917
328	41918
329	41930
330	41932
331	41934
332	41935
333	41936
334	41866
335	41919
336	41920
337	41921
338	41922
339	41924
340	41926
341	41927
342	41929
343	41938
344	41923
345	41517
346	41893
347	41894
348	41895
349	41896
350	41903
351	41904
352	41905
353	41906
354	41907
355	41909
356	41912
357	41913
358	41864
359	41890
360	41892
361	41897
362	41898
363	41899
364	41900
365	41901
366	41902
367	41914
368	41876
369	41888
370	41908
371	41859
372	41886
373	41887
374	41889
375	41891
376	41479
377	41862
378	41871
379	41873
380	41874
381	41875
382	41878
383	41879
384	41880
385	41881
386	41882
387	41883
388	41884
389	41885
390	41518
391	41855
392	41860
393	41861
394	41867
395	41868
396	41869
397	41872
398	41877
399	41829
400	41853
401	41854
402	41856
403	41858
404	41863
405	41865
406	41870
407	41537
408	41841
409	41842
410	41843
411	41851
412	41857
413	41780
414	41810
415	41840
416	41844
417	41845
418	41846
419	41847
420	41848
421	41850
422	41852
423	41773
424	41826
425	41830
426	41833
427	41834
428	41836
429	41837
430	41838
431	41788
432	41821
433	41823
434	41827
435	41828
436	41835
437	41839
438	41824
439	41825
440	41831
441	41832
442	41801
443	41809
444	41811
445	41814
446	41815
447	41817
448	41818
449	41820
450	41763
451	41805
452	41803
453	41804
454	41806
455	41807
456	41808
457	41813
458	41816
459	41819
460	41822
461	41776
462	41792
463	41793
464	41764
465	41786
466	41791
467	41794
468	41796
469	41797
470	41799
471	41812
472	41790
473	41795
474	41800
475	41762
476	41777
477	41784
478	41785
479	41789
480	41802
481	41735
482	41775
Sheet2

 

[willow1985]

IN Sheet CC - column E
=IF(AND(C2="",D2=""),"",IF(OR(COUNTIFS(Sheet1!A:A,CC!C2)>0,COUNTIFS(Sheet2!C:C,CC!C2)>0 ),"YES",IF(OR(COUNTIFS(Sheet1!A:A,CC!D2)>0,COUNTIFS(Sheet2!C:C,CC!D2)>0 ), "YES","NO")))

This is Checking to see if the Number in Sheet CC in C2 matches either Sheet1 column A or Sheet 2 column C
Using
OR(COUNTIFS(Sheet1!A:A,CC!C2)>0,COUNTIFS(Sheet2!C:C,CC!C2)>0 )

Then same for column D
OR(COUNTIFS(Sheet1!A:A,CC!D2)>0,COUNTIFS(Sheet2!C:C,CC!D2)>0 )

then added to a NESTED IF to return a YES or a NO

as its across multiple sheets
I thought rather than XL2BB
I would add to dropbox

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I got it now, thank you. This works but I did find another problem which is forcing me to scrap the entire thing. I would require 2 conditions before it even does the match formula: If column H on Sheet "CC" says "Dept 1" AND if column I on Sheet 2 says "Dept 1" then Match.
I did not include that additional data but just discovered there are duplicate file numbers between departments.

Thank you All for your help and I hope this helps someone in the future!

 

[etaf]

then on the IF we could add another IF
If column H on Sheet "CC" says "Dept 1" AND if column I on Sheet 2 says "Dept 1" then Match.
IF( AND( H2<>"dept 1", I2 <> "Dept 1") , "", IF(.......

 

[willow1985]

Thank you

 

[etaf]

you are welcome