vlookup and hlookup on multiple criteria

[chungtinhlak]

I have a table with two row header and one column header.

My row header is Month on a1 and down, on B1 and down, I have name and these names many repeate each month.

On my column, I have type such as backlong, on hold...

I was wondering if there is a way to use vlookup to look up for example

In december (vlookup), how many backlong(hlookup), for a name (hlookup again)?

Please see my attach file for example

[Bob Phillips]

Just use a formula of

=SUMPRODUCT(--($A$2:$A$28=$A35),--($B$2:$B$28=$B35),--ISNUMBER(C$2:C$28))

copy down and across

[chungtinhlak]

Thanks xld, but what happen if the column header are not in the same order as the table provided. Please see attached.

[Bob Phillips]

No problem

=SUMPRODUCT(($A$2:$A$28=$A35)*($B$2:$B$28=$B35)
*($C$1:$I$1=C$34)*(ISNUMBER($C$2:$I$28)))

[chungtinhlak]

thanks xld, I use it but i only get a value of one or 2 back? Please help.

[Bob Phillips]

I see two 2s and two 1s there.

[chungtinhlak]

really, I highlighted the return value and the actual value in the attached file. They don't match.

[Bob Phillips]

Are you saying that you want to sum them not count?

=SUMPRODUCT(($A$2:$A$28=$A35)*($B$2:$B$28=$B35)*($C$1:$I$1=C$34)
*(ISNUMBER($C$2:$I$28))*($C$2:$I$28))

[chungtinhlak]

can we display that actual value of the cell instead?

[Bob Phillips]

Which value? Ther can be more than 1.

[chungtinhlak]

I hightlighted the value that I want return in the attached file. Thanks so much for you help xld

[Bob Phillips]

It is not sufficient to show which you want, you need to explain why. Why the second Feb-09 for Thaison, why not the first.

[chungtinhlak]

I'm sorry, I made a mistake, there should only be one thaison. For each month, each name can only appear there once. If twice, we pick either one.