Vlookup requiring 2 columns to match before presenting data.may include multiple rows

[Shane21882]

Hi All,

I am making a reconciliation process for agency staff invoices we pay out. There is one formula I need that I will use quite a few times in this.

From the attachment I have created a dummy with one scenario. Tab "invoice approval log" contains invoices as we log that have been approved for payment. This includes staff ID numbers, the week ending date the invoice relates to and the net value. Tab "timesheet costs" is the costs created by the timesheet system when employees enter their timesheets. Here I need to check what we pay out matches what the timesheet systems says we should pay out. In the column highlighted in orange, i would like a lookup that will check columns A and B (staff ID and timesheet w/e and find the matches on tab "invoice approval.." and then provide the net value. Also in tab 2 there maybe more than 1 invoice for the same staff ID number and W/E so it needs to add all matches up. I guess a pivot table could be used first for this to total items up but would be nice if this wasn't necessary.

Cheers,

Shane

[ChemistB]

Try this in 'Timesheet costs'!E4

=SUMIFS('Invoice approval log'!$F$4:$F$35,'Invoice approval log'!$B$4:$B$35,A4,'Invoice approval log'!$C$4:$C$35, B4)
Is that what you are looking for?

[Shane21882]

Ah that will do the trick cheers. I put it in that cell but changed the columns to the letters as below as it came up with zero, but works now. Cheers

=SUMIFS('Invoice approval log'!$E$4:$E$35,'Invoice approval log'!$A$4:$A$35,A4,'Invoice approval log'!$B$4:$B$35, B4)

[Shane21882]

ah how do I make this solved? lol

[DGagnon]

If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

[Shane21882]

All done

solved