PDA

View Full Version : vlookup and hlookup on multiple criteria



chungtinhlak
05-07-2009, 12:29 PM
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
05-07-2009, 01:07 PM
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
05-07-2009, 01:15 PM
Thanks xld, but what happen if the column header are not in the same order as the table provided. Please see attached.

Bob Phillips
05-07-2009, 02:52 PM
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
05-08-2009, 08:44 AM
thanks xld, I use it but i only get a value of one or 2 back? Please help.

Bob Phillips
05-08-2009, 09:31 AM
I see two 2s and two 1s there.

chungtinhlak
05-08-2009, 10:04 AM
really, I highlighted the return value and the actual value in the attached file. They don't match.

Bob Phillips
05-08-2009, 10:39 AM
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
05-08-2009, 12:12 PM
can we display that actual value of the cell instead?

Bob Phillips
05-08-2009, 02:32 PM
Which value? Ther can be more than 1.

chungtinhlak
05-09-2009, 08:13 AM
I hightlighted the value that I want return in the attached file. Thanks so much for you help xld

Bob Phillips
05-09-2009, 09:54 AM
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
05-09-2009, 12:01 PM
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.