vlookup using multiple conditions

[esaban]

attached is a very condensed version of a file that I need to lookup multiple criteria. i tried using an index and match, but couldn't get it to work.

essentially what i need is to enter a formula on tab 'main' c2 that lookups and matches the array table 'payroll'.

for example, on jan 2, agent Tom worked 8 hours

[daffodil11]

=SUMPRODUCT((Payroll!$A$2:$A$13=Main!A2)*(Payroll!$B$2:$B$13=Main!B2)*(Payroll!$C$2:$C$13))

Tom is listed twice for Jan 2. He worked a total of 9 hours.

Assuming all records are unique and there are no duplicate entries as there are in the example, you could use a few other options:

=LOOKUP(2,1/((Payroll!$A$2:$A$13=Main!A2)*(Payroll!$B$2:$B$13=Main!B2)),Payroll!$C$2:$C$13)

OR

Array Formulas

=INDEX(Payroll!$C$2:$C$13,MATCH(A2&B2,Payroll!$A$2:$A$13&Payroll!$B$2:$B$13,0))
=INDEX(Payroll!$C$2:$C$13,MATCH(A2, IF(Payroll!$B$2:$B$13=B2,Payroll!$A$2:$A$13),0))

* Array formulas are confirmed with Ctrl+Shift+Enter


They all do similar functions. SUMPRODUCT will add up everything that meets the criteria. INDEX will match to the first record that meets criteria, and LOOKUP will match to the last. Assuming unique entries, everything is the same.

[BadlySpelledBuoy]

Try the below formula in cell C2 on the "Main" worksheet.

=IFERROR(INDEX(Payroll!A$2:C$13,MATCH(1,(Payroll!A$2:A$13=A2)*(Payroll!B$2:B$13=B2),0),3),"")

It's an array formula so must be confirmed with Ctrl+Shift+Enter rather than just Enter.

EDIT: Oops, didn't notice the duplication of names on the same date. That means my formula doesn't work, unless that was an error..

[daffodil11]

I assume it was an accidental oversight in generating sample data.

[esaban]

correct, the duplicate data was an error