excel 2003: how to match records of 2 tables according to 2 columns?

[nk]

I'd like to add 1 to column E in table 1 whenever the data in columns
B & C are identical to the data in columns A & E in table 2

Table 1:
A B C D E F G
1 39174 200101 -173 30/06/07 1 31/03/09 510911019
2 39184 200101 -4231 30/06/07 1 31/03/09 510911019
3 39188 200101 -1343 30/06/07 1 31/03/09 510911019
4 39188 200101 -1733 30/06/07 1 31/03/09 510911019
5 39191 200101 -785 30/06/07 1 31/03/09 510911019
6 39191 200101 -17675 30/06/07 1 31/03/09 510911019
7 39142 200111 -394 30/06/07 1 31/03/09 510853468
8 39189 200111 -935 30/06/07 1 31/03/09 510853468
9 39203 200111 -328 30/08/07 1 31/03/09 510853468
10 39231 200111 -35 31/07/07 1 31/03/09 510853468
11 39149 200117 -53 30/06/07 1 31/03/09 511193898
12 39222 200117 -173 31/08/07 1 31/03/09 511193898
13 39238 200117 -162 30/09/07 1 31/03/09 511193898
14 39148 200129 -704.91 30/06/07 1 31/03/09 511687402
15 39141 200135 -25048 30/04/07 1 31/03/08 510917388
16 39145 200140 -1107 31/05/07 1 12/01/07 513751974


Table 2:
A B C E G J
1 200101 39174 dfggf -173 39295 1
2 200101 39184 teet -4231 39295 1
3 200101 39188 v -1733 39295 1
4 200101 39191 nvn -785 39295 1
5 200101 39191 bcbd -17675 39295 1
6 200101 39188 g -1343 39295 1
7 200111 39142 dgg -394 39278 1
8 200111 39189 gdgd -935 39278 1
9 200111 39231 dgdgd -35 39278 1
10 200111 39203 -328 39278 1
11 200117 39149 gdgd -53 39278 1
12 200117 39222 jlj -173 39278 1
13 200117 39222 bnm -173 39278 1
14 200117 39238 byr -162 39278 1
15 200117 39238 y -162 39278 1
16 200129 39148 bfh -704.91 39278 1
17 200135 39141 wr -25048 39278 1
18 200140 39145 dgg -1107 39278 1

 

[Guest]

Hi nk,

if identical, add 1?

pls verify below
On table 1
A B C D E F G
13 39238 200117 -162 30/09/07 1 31/03/09 511193898

then on table 2
A B C E G J
12 200117 39222 jlj -173 39278 1
13 200117 39222 bnm -173 39278 1
--------------
What shall be your correct result in the above sample...1+2=3 or 1+1=2 or
only 1
Table 2 shows 2times of the identical criteria...by Column with group of rows

regards ,
driller

 

[Guest]

nk,
not quite sure, maybe not this trial formula, but just for a re-starter...

I'd like to add 1 to column E in table 1 whenever the data in columns
B & C are identical to the data in columns A & E in table 2

*whenever*
=SUMPRODUCT((B1=Sheet2!$A$1:$A$18)*(C1=Sheet2!$E$1:$E$18))

Table 1 on e.g. current sheet
Table 2 on e.g. Sheet2