vlookup, match? what do I use?
 

Please help. Here a a sample of my data:

Table 1 Table 2
A B A B
C D
1 id category id MT JL OT
2 41 MT 41
3 41 JL 45
4 41 MT 47
5 41 JL 53
6 45 JL 54
7 45 JL 55
8 45 MT 56
9 45 MT 57

I want excel to lookup "Table 2 A2" in "Table 1 A:A" (which has 1493 rows of
data). If it finds a match I want it to count the number of Table 2 B1 that
appear under that match and put the answer in Table B2. I will repeat this
for C1, D1 etc.

vlookup, match? what do I use?
 

=COUNTIF(Table1!A:A,A2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sojo" wrote in message
...
Please help. Here a a sample of my data:

Table 1 Table 2
A B A B
C D
1 id category id MT JL OT
2 41 MT 41
3 41 JL 45
4 41 MT 47
5 41 JL 53
6 45 JL 54
7 45 JL 55
8 45 MT 56
9 45 MT 57

I want excel to lookup "Table 2 A2" in "Table 1 A:A" (which has 1493 rows
of
data). If it finds a match I want it to count the number of Table 2 B1
that
appear under that match and put the answer in Table B2. I will repeat
this
for C1, D1 etc.

vlookup, match? what do I use?
 

Enter that formula in Table2 cell B2
=SUMPRODUCT( ( 'Table 1'!$A$2:$A$1500 = $A2 ) * ( 'Table 1'!$B$2:$B$1500 =
B$1 ) )

"Drag-fill" as needed...
--
Festina Lente


"Sojo" wrote:

Please help. Here a a sample of my data:

Table 1 Table 2
A B A B
C D
1 id category id MT JL OT
2 41 MT 41
3 41 JL 45
4 41 MT 47
5 41 JL 53
6 45 JL 54
7 45 JL 55
8 45 MT 56
9 45 MT 57

I want excel to lookup "Table 2 A2" in "Table 1 A:A" (which has 1493 rows of
data). If it finds a match I want it to count the number of Table 2 B1 that
appear under that match and put the answer in Table B2. I will repeat this
for C1, D1 etc.