Table 1 and Table 2 both contain a common vendor ID in column A. Table 2
contains caseload counts in column C associated with Client types in column
B. In Table 1, columns B, I want to create a formula which will match the
value in column A with the same value in Table 2, column A. I then want the
formula in table 1 to return the value in table 2, column C if the value in
table 2, column B is €œclient type 1€�. Any suggestions?

Table 1:
A B
Vendor Client Type 1
2 23
4
5 15

Table 2:
A B C
Vendor Client Type Caseload
2 Client Type 1 23
2 Client Type 2 42
4 Client Type 2 15
5 Client Type 1 15
5 Client Type 3 14

Hi,

Why don't you show us some dummy data because the approach might differ if
the data type is different.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Daniel Collison" wrote:

Table 1 and Table 2 both contain a common vendor ID in column A. Table 2
contains caseload counts in column C associated with Client types in column
B. In Table 1, columns B, I want to create a formula which will match the
value in column A with the same value in Table 2, column A. I then want the
formula in table 1 to return the value in table 2, column C if the value in
table 2, column B is €œclient type 1€�. Any suggestions?

Table 1:
A B
Vendor Client Type 1
2 23
4
5 15

Table 2:
A B C
Vendor Client Type Caseload
2 Client Type 1 23
2 Client Type 2 42
4 Client Type 2 15
5 Client Type 1 15
5 Client Type 3 14

=sumproduct((Table2!A2:A6=$A2)*(Table2!B2:B6=B$1)* (Table2!C2:C6))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Daniel Collison" wrote in
message ...
Table 1 and Table 2 both contain a common vendor ID in column A. Table 2
contains caseload counts in column C associated with Client types in
column
B. In Table 1, columns B, I want to create a formula which will match the
value in column A with the same value in Table 2, column A. I then want
the
formula in table 1 to return the value in table 2, column C if the value
in
table 2, column B is €œclient type 1€�. Any suggestions?

Table 1:
A B
Vendor Client Type 1
2 23
4
5 15

Table 2:
A B C
Vendor Client Type Caseload
2 Client Type 1 23
2 Client Type 2 42
4 Client Type 2 15
5 Client Type 1 15
5 Client Type 3 14

The data type for the provider will be text (i.e. it could include "2" and
"45a". The data type for the client type will always be text. And the data
type for the caseload figures will always be a number. Is this helpful?
Thanks...DC


"Daniel Collison" wrote:

Table 1 and Table 2 both contain a common vendor ID in column A. Table 2
contains caseload counts in column C associated with Client types in column
B. In Table 1, columns B, I want to create a formula which will match the
value in column A with the same value in Table 2, column A. I then want the
formula in table 1 to return the value in table 2, column C if the value in
table 2, column B is €œclient type 1€�. Any suggestions?

Table 1:
A B
Vendor Client Type 1
2 23
4
5 15

Table 2:
A B C
Vendor Client Type Caseload
2 Client Type 1 23
2 Client Type 2 42
4 Client Type 2 15
5 Client Type 1 15
5 Client Type 3 14

Hi,

Here are two alternatives, the first is an array:

=INDEX(C$8:C$12,MATCH($A2&B$1,$A$8:$A$12&$B$8:$B$1 2,0),)

array - you must enter it by pressing Shift+Ctrl+Enter. This formula return
an NA message if no match is found.

in 2007 you can use

=SUMIFS($C$8:$C$12,$A$8:$A$12,$A2,$B$8:$B$12,B$1)

Ashish already gave you the non-array 2003 formula

=SUMPRODUCT(--($A$8:$A$12=$A2),--($B$8:$B$12=B$1),$C$8:$C$12)

or to be specific a version that is similar to the above. I've included
absolute cell references to allow you to copy the formula down or to the
right.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Daniel Collison" wrote:

The data type for the provider will be text (i.e. it could include "2" and
"45a". The data type for the client type will always be text. And the data
type for the caseload figures will always be a number. Is this helpful?
Thanks...DC


"Daniel Collison" wrote:

Table 1 and Table 2 both contain a common vendor ID in column A. Table 2
contains caseload counts in column C associated with Client types in column
B. In Table 1, columns B, I want to create a formula which will match the
value in column A with the same value in Table 2, column A. I then want the
formula in table 1 to return the value in table 2, column C if the value in
table 2, column B is €œclient type 1€�. Any suggestions?

Table 1:
A B
Vendor Client Type 1
2 23
4
5 15

Table 2:
A B C
Vendor Client Type Caseload
2 Client Type 1 23
2 Client Type 2 42
4 Client Type 2 15
5 Client Type 1 15
5 Client Type 3 14

Shane:

THanks very much...the INDEX formula worked perfectly.

Daniel

"Shane Devenshire" wrote:

Hi,

Here are two alternatives, the first is an array:

=INDEX(C$8:C$12,MATCH($A2&B$1,$A$8:$A$12&$B$8:$B$1 2,0),)

array - you must enter it by pressing Shift+Ctrl+Enter. This formula return
an NA message if no match is found.

in 2007 you can use

=SUMIFS($C$8:$C$12,$A$8:$A$12,$A2,$B$8:$B$12,B$1)

Ashish already gave you the non-array 2003 formula

=SUMPRODUCT(--($A$8:$A$12=$A2),--($B$8:$B$12=B$1),$C$8:$C$12)

or to be specific a version that is similar to the above. I've included
absolute cell references to allow you to copy the formula down or to the
right.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Daniel Collison" wrote:

The data type for the provider will be text (i.e. it could include "2" and
"45a". The data type for the client type will always be text. And the data
type for the caseload figures will always be a number. Is this helpful?
Thanks...DC


"Daniel Collison" wrote:

Table 1 and Table 2 both contain a common vendor ID in column A. Table 2
contains caseload counts in column C associated with Client types in column
B. In Table 1, columns B, I want to create a formula which will match the
value in column A with the same value in Table 2, column A. I then want the
formula in table 1 to return the value in table 2, column C if the value in
table 2, column B is €œclient type 1€�. Any suggestions?

Table 1:
A B
Vendor Client Type 1
2 23
4
5 15

Table 2:
A B C
Vendor Client Type Caseload
2 Client Type 1 23
2 Client Type 2 42
4 Client Type 2 15
5 Client Type 1 15
5 Client Type 3 14