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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
713
Office Version
  1. 365
Platform
  1. Windows
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
BCD
1Latest File #Previous File #Match Yes/No
2
3
4102700
5
6
768720
868721
9
1068764
11N/A
1268781
1368784
1468901
1555283
1681008
1781034
1880893
1968969
20105362
21105627
22105651
23N/A
248026878814
2568904
2668902
2768914
2868915
2968903
3069086
3169087
3280815
33105685
34105688
3580940
368094668201
37105978
38105864
39105867
40335020334888
4169356
4281029
4369385
4469386
4569399
4669470
4781185
4869495
49N/A
50163181
51N/A
5255421
5368995
5469667
5581821
5646780
5724202
5869725
5969787
60106682
615500247734
6281797
6382152
6481482
65335273
6669863
6781911
6882085
6969877
70336045
7170021
7270039
7369910
7470000
7557282
7670149
77104247104246
78107178
7980964
8082349
8182418
8270218
8370233
848179481793
8570243
8670265
87N/A
8870367
8970385
9070312
9181155
9269643
9382692
9470487
9570501
9670502
97219016
9882973
9983251
10081906
101337084
10270252
10338297
10470666
105107913
106101923
107All
108N/A
10938297
110336777
111N/A
112107863107536
113102037
114
11570846
CC


Book1
A
1File Number
2213172
3213171
4213170
5213169
6213168
7213167
8213166
9213165
10213164
11213163
12213162
13213161
14213160
15213159
16213158
17213157
18213156
19213155
20213154
21213153
22213152
23213151
24213150
25213149
26213148
27213147
28213146
29213145
30213144
31213143
32213142
33213141
34213140
35213139
36213138
37213137
38213136
39213135
40213134
41213133
42213132
43213131
44213130
45213129
46213128
47213127
48213126
49213125
50213124
51213123
52213122
53213121
54213120
55213119
56213118
57213117
58213116
59213115
60213114
61213113
62213112
63213111
64213110
65213109
66213108
67213107
68213106
69213105
70213104
71213103
72213102
73213101
74213100
75213099
76213098
77213097
78213096
79213095
80213094
81213093
82213092
83213091
84213090
85213089
86213088
87213087
88213086
89213085
90213084
91213083
92213082
93213081
94213080
95213079
96213078
97213077
98213076
99213075
100213074
101213073
102213072
103213071
104213070
105213069
106213068
107213067
108213066
109213065
110213064
111213063
112213062
113213061
114213060
115213059
116213058
117213057
118213056
119213055
120213054
121213053
122213052
123213051
124213050
125213049
126213048
127213047
128213046
129213045
130213044
131213043
132213042
133213041
134213040
135213039
136213038
137213037
138213036
139213035
140213034
141213033
142213032
143213031
144213030
145213029
146213028
147213027
148213026
149213025
150213024
151213023
152213022
153213021
154213020
155213019
156213018
157213017
158213016
159213015
160213014
161213013
162213012
163213011
164213010
165213009
166213008
167213007
168213006
169213005
170213004
171213003
172213002
173213001
174213000
175212999
176212998
177212997
178212996
179212995
180212994
181212993
182212992
183212991
184212990
185212989
186212988
187212987
188212986
189212985
190212984
191212983
192212982
193212981
194212980
195212979
196212978
197212977
198212976
199212975
200212974
201212973
Sheet1


Book1
C
1File Number
242244
342243
442195
542205
642245
742248
842249
942237
1042250
1142251
1242159
1342252
1442253
1542239
1642246
1742226
1842227
1942232
2042234
2142240
2242241
2342242
2442247
2542223
2642233
2742235
2842236
2942228
3042229
3142230
3242231
3342238
3442221
3542224
3642225
3741536
3842215
3942217
4042220
4142222
4242175
4342176
4442177
4542178
4642179
4742180
4842189
4942198
5042214
5142216
5242218
5342219
5442194
5542196
5642197
5742201
5842206
5942207
6042209
6142210
6242211
6342212
6442213
6542208
6642069
6742191
6842192
6942193
7042199
7142200
7242202
7342204
7442183
7542188
7642173
7742187
7842190
7942166
8042184
8142185
8242186
8342130
8442168
8542181
8642182
8742161
8842164
8942167
9042170
9142172
9242174
9342157
9442171
9542158
9642160
9742162
9842163
9942169
10042150
10142152
10242153
10342139
10442141
10542142
10642143
10742147
10842148
10942149
11042154
11142155
11242156
11341478
11442138
11542144
11642145
11742151
11842131
11942132
12042133
12142137
12242140
12342146
12442120
12542121
12642123
12742124
12842127
12942128
13042129
13142134
13242135
13342136
13442079
13542108
13642117
13742118
13842122
13942125
14042126
14140928
14242113
14342116
14442119
14542104
14642109
14742110
14842111
14942112
15042114
15142115
15242080
15342081
15442103
15542105
15642106
15742107
15842090
15942102
16042097
16142098
16242064
16342065
16442092
16542093
16642099
16742101
16842066
16942067
17042068
17142082
17242083
17342085
17442087
17542094
17642095
17742075
17842076
17942078
18042084
18142086
18242089
18342096
18442100
18542070
18642074
18742077
18842091
18942063
19042071
19142088
19242050
19342060
19442062
19542072
19642073
19742051
19842055
19942056
20042057
20142058
20242061
20342031
20442047
20542059
20642038
20742043
20842044
20942045
21042046
21142048
21242049
21342053
21442025
21542034
21642035
21742039
21842040
21942054
22042013
22142032
22242033
22342041
22442009
22542012
22642016
22742020
22842029
22942036
23042037
23142042
23242019
23342021
23442022
23542023
23642024
23742026
23842027
23942028
24042030
24142011
24242018
24342004
24442005
24542010
24642014
24742015
24841988
24941989
25042017
25142000
25242007
25341849
25441965
25542001
25642006
25742008
25841990
25941991
26041994
26141995
26241998
26342002
26442003
26541979
26641984
26741986
26841992
26941996
27041997
27141999
27241974
27341977
27441985
27541987
27641993
27741972
27841973
27941981
28041982
28141960
28241975
28341976
28441978
28541980
28641983
28741956
28841957
28941959
29041961
29141962
29241966
29341967
29441968
29541969
29641970
29741971
29841951
29941953
30041954
30141955
30241958
30341963
30441964
30541937
30641947
30741950
30841943
30941949
31041940
31141945
31241946
31341952
31441925
31541928
31641931
31741933
31841939
31941941
32041942
32141944
32241948
32341910
32441911
32541915
32641916
32741917
32841918
32941930
33041932
33141934
33241935
33341936
33441866
33541919
33641920
33741921
33841922
33941924
34041926
34141927
34241929
34341938
34441923
34541517
34641893
34741894
34841895
34941896
35041903
35141904
35241905
35341906
35441907
35541909
35641912
35741913
35841864
35941890
36041892
36141897
36241898
36341899
36441900
36541901
36641902
36741914
36841876
36941888
37041908
37141859
37241886
37341887
37441889
37541891
37641479
37741862
37841871
37941873
38041874
38141875
38241878
38341879
38441880
38541881
38641882
38741883
38841884
38941885
39041518
39141855
39241860
39341861
39441867
39541868
39641869
39741872
39841877
39941829
40041853
40141854
40241856
40341858
40441863
40541865
40641870
40741537
40841841
40941842
41041843
41141851
41241857
41341780
41441810
41541840
41641844
41741845
41841846
41941847
42041848
42141850
42241852
42341773
42441826
42541830
42641833
42741834
42841836
42941837
43041838
43141788
43241821
43341823
43441827
43541828
43641835
43741839
43841824
43941825
44041831
44141832
44241801
44341809
44441811
44541814
44641815
44741817
44841818
44941820
45041763
45141805
45241803
45341804
45441806
45541807
45641808
45741813
45841816
45941819
46041822
46141776
46241792
46341793
46441764
46541786
46641791
46741794
46841796
46941797
47041799
47141812
47241790
47341795
47441800
47541762
47641777
47741784
47841785
47941789
48041802
48141735
48241775
Sheet2
 

Attachments

  • matches.jpg
    matches.jpg
    212.5 KB · Views: 5

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,574
Office Version
  1. 365
Platform
  1. MacOS
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
 
Last edited:

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
713
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,574
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,574
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,848
Office Version
  1. 2016
Platform
  1. Windows
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
BCD
1Latest File #Previous File #Match Yes/No
2213172Yes
342244Yes
4102700No
5No
6No
768720No
868721No
9No
1068764No
11N/ANo
1268781No
1368784No
1468901No
1555283No
1681008No
1781034No
1880893No
1968969No
20105362No
21105627No
22105651No
23N/ANo
248026878814No
2568904No
2668902No
2768914No
2868915No
2968903No
3069086No
3169087No
3280815No
33105685No
34105688No
3580940No
368094668201No
37105978No
38105864No
39105867No
40335020334888No
4169356No
4281029No
4369385No
4469386No
4569399No
4669470No
4781185No
4869495No
49N/ANo
50163181No
51N/ANo
5255421No
5368995No
5469667No
5581821No
5646780No
5724202No
5869725No
5969787No
60106682No
615500247734No
6281797No
6382152No
6481482No
65335273No
6669863No
6781911No
6882085No
6969877No
70336045No
7170021No
7270039No
7369910No
7470000No
7557282No
7670149No
77104247104246No
78107178No
7980964No
8082349No
8182418No
8270218No
8370233No
848179481793No
8570243No
8670265No
87N/ANo
8870367No
8970385No
9070312No
9181155No
9269643No
9382692No
9470487No
9570501No
9670502No
97219016No
9882973No
9983251No
10081906No
101337084No
10270252No
10338297No
10470666No
105107913No
106101923No
107AllNo
108N/ANo
10938297No
110336777No
111N/ANo
112107863107536No
113102037No
114No
11570846No
CC
Cell Formulas
RangeFormula
D2:D115D2=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
1File Number
2213172
3213171
4213170
5213169
6213168
7213167
8213166
9213165
10213164
11213163
12213162
13213161
14213160
15213159
16213158
17213157
18213156
19213155
20213154
21213153
22213152
23213151
24213150
25213149
26213148
27213147
28213146
29213145
30213144
31213143
32213142
33213141
34213140
35213139
36213138
37213137
38213136
39213135
40213134
41213133
42213132
43213131
44213130
45213129
46213128
47213127
48213126
49213125
50213124
51213123
52213122
53213121
54213120
55213119
56213118
57213117
58213116
59213115
60213114
61213113
62213112
63213111
64213110
65213109
66213108
67213107
68213106
69213105
70213104
71213103
72213102
73213101
74213100
75213099
76213098
77213097
78213096
79213095
80213094
81213093
82213092
83213091
84213090
85213089
86213088
87213087
88213086
89213085
90213084
91213083
92213082
93213081
94213080
95213079
96213078
97213077
98213076
99213075
100213074
101213073
102213072
103213071
104213070
105213069
106213068
107213067
108213066
109213065
110213064
111213063
112213062
113213061
114213060
115213059
116213058
117213057
118213056
119213055
120213054
121213053
122213052
123213051
124213050
125213049
126213048
127213047
128213046
129213045
130213044
131213043
132213042
133213041
134213040
135213039
136213038
137213037
138213036
139213035
140213034
141213033
142213032
143213031
144213030
145213029
146213028
147213027
148213026
149213025
150213024
151213023
152213022
153213021
154213020
155213019
156213018
157213017
158213016
159213015
160213014
161213013
162213012
163213011
164213010
165213009
166213008
167213007
168213006
169213005
170213004
171213003
172213002
173213001
174213000
175212999
176212998
177212997
178212996
179212995
180212994
181212993
182212992
183212991
184212990
185212989
186212988
187212987
188212986
189212985
190212984
191212983
192212982
193212981
194212980
195212979
196212978
197212977
198212976
199212975
200212974
201212973
Sheet1


Book1
C
1File Number
242244
342243
442195
542205
642245
742248
842249
942237
1042250
1142251
1242159
1342252
1442253
1542239
1642246
1742226
1842227
1942232
2042234
2142240
2242241
2342242
2442247
2542223
2642233
2742235
2842236
2942228
3042229
3142230
3242231
3342238
3442221
3542224
3642225
3741536
3842215
3942217
4042220
4142222
4242175
4342176
4442177
4542178
4642179
4742180
4842189
4942198
5042214
5142216
5242218
5342219
5442194
5542196
5642197
5742201
5842206
5942207
6042209
6142210
6242211
6342212
6442213
6542208
6642069
6742191
6842192
6942193
7042199
7142200
7242202
7342204
7442183
7542188
7642173
7742187
7842190
7942166
8042184
8142185
8242186
8342130
8442168
8542181
8642182
8742161
8842164
8942167
9042170
9142172
9242174
9342157
9442171
9542158
9642160
9742162
9842163
9942169
10042150
10142152
10242153
10342139
10442141
10542142
10642143
10742147
10842148
10942149
11042154
11142155
11242156
11341478
11442138
11542144
11642145
11742151
11842131
11942132
12042133
12142137
12242140
12342146
12442120
12542121
12642123
12742124
12842127
12942128
13042129
13142134
13242135
13342136
13442079
13542108
13642117
13742118
13842122
13942125
14042126
14140928
14242113
14342116
14442119
14542104
14642109
14742110
14842111
14942112
15042114
15142115
15242080
15342081
15442103
15542105
15642106
15742107
15842090
15942102
16042097
16142098
16242064
16342065
16442092
16542093
16642099
16742101
16842066
16942067
17042068
17142082
17242083
17342085
17442087
17542094
17642095
17742075
17842076
17942078
18042084
18142086
18242089
18342096
18442100
18542070
18642074
18742077
18842091
18942063
19042071
19142088
19242050
19342060
19442062
19542072
19642073
19742051
19842055
19942056
20042057
20142058
20242061
20342031
20442047
20542059
20642038
20742043
20842044
20942045
21042046
21142048
21242049
21342053
21442025
21542034
21642035
21742039
21842040
21942054
22042013
22142032
22242033
22342041
22442009
22542012
22642016
22742020
22842029
22942036
23042037
23142042
23242019
23342021
23442022
23542023
23642024
23742026
23842027
23942028
24042030
24142011
24242018
24342004
24442005
24542010
24642014
24742015
24841988
24941989
25042017
25142000
25242007
25341849
25441965
25542001
25642006
25742008
25841990
25941991
26041994
26141995
26241998
26342002
26442003
26541979
26641984
26741986
26841992
26941996
27041997
27141999
27241974
27341977
27441985
27541987
27641993
27741972
27841973
27941981
28041982
28141960
28241975
28341976
28441978
28541980
28641983
28741956
28841957
28941959
29041961
29141962
29241966
29341967
29441968
29541969
29641970
29741971
29841951
29941953
30041954
30141955
30241958
30341963
30441964
30541937
30641947
30741950
30841943
30941949
31041940
31141945
31241946
31341952
31441925
31541928
31641931
31741933
31841939
31941941
32041942
32141944
32241948
32341910
32441911
32541915
32641916
32741917
32841918
32941930
33041932
33141934
33241935
33341936
33441866
33541919
33641920
33741921
33841922
33941924
34041926
34141927
34241929
34341938
34441923
34541517
34641893
34741894
34841895
34941896
35041903
35141904
35241905
35341906
35441907
35541909
35641912
35741913
35841864
35941890
36041892
36141897
36241898
36341899
36441900
36541901
36641902
36741914
36841876
36941888
37041908
37141859
37241886
37341887
37441889
37541891
37641479
37741862
37841871
37941873
38041874
38141875
38241878
38341879
38441880
38541881
38641882
38741883
38841884
38941885
39041518
39141855
39241860
39341861
39441867
39541868
39641869
39741872
39841877
39941829
40041853
40141854
40241856
40341858
40441863
40541865
40641870
40741537
40841841
40941842
41041843
41141851
41241857
41341780
41441810
41541840
41641844
41741845
41841846
41941847
42041848
42141850
42241852
42341773
42441826
42541830
42641833
42741834
42841836
42941837
43041838
43141788
43241821
43341823
43441827
43541828
43641835
43741839
43841824
43941825
44041831
44141832
44241801
44341809
44441811
44541814
44641815
44741817
44841818
44941820
45041763
45141805
45241803
45341804
45441806
45541807
45641808
45741813
45841816
45941819
46041822
46141776
46241792
46341793
46441764
46541786
46641791
46741794
46841796
46941797
47041799
47141812
47241790
47341795
47441800
47541762
47641777
47741784
47841785
47941789
48041802
48141735
48241775
Sheet2
 
Last edited:

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
713
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Well-known Member
Joined
Oct 24, 2012
Messages
4,574
Office Version
  1. 365
Platform
  1. MacOS
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(.......
 
Solution

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,574
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,127,050
Messages
5,622,414
Members
415,895
Latest member
Akhilesh28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top