vlookup with 2 criteria

[Daniel Collison]

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 Devenshire]

Hi,

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

 

[Ashish Mathur]

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Daniel Collison]

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

 

[Shane Devenshire]

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$12,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.

 

[Daniel Collison]

Shane:

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

Daniel