HELP: Vlookup that needs to match dates

Hi Guys,

Hoping for some help here, been on this for a while. I have attached an example of what I am working on at the moment. Basically I need to determine the revenue of multiple customers for purchases on a specific date.

I have a sheet called "Returned" that I would like to put the value of the original purchase into. Now the "Purchased" tab has all the purchases from my customers, however I only want to know what the original revenue was on the date "Serviced" by that "Person ID." Sometimes the serviced date in the "Returned" sheet will not exist in the "Purchased" sheet to add to the complexity, in which case I would need it to equal 0.

For example, cell K2 needs to equal 595 and K3 needs to equal 0, what is the formula I need to look this up?

Thanks,
Jason.

Originally Posted by etaf

can you use the invoice number at all

=IFERROR(INDEX(Purchased!$H$2:$H$23,MATCH(B2,Purchased!$B$2:$B$23,0)),0)

I cannot use the invoice number as sometimes they are incorrect.

Originally Posted by daffodil11

In K2:

=IFERROR(INDEX(Purchased!$H$2:$H$23,MATCH(Returned!A2,IF(Purchased!$I$2:$I$23=Returned!I2,Purchased!$A$2:$A$23),0)),0)

This is an array formula, confirmed using Ctrl+Shift+Enter to exit the cell after the formula is entered, instead of hitting Enter normally.

Drag down as needed.

This works GREAT! However one problem, what if I have duplicate dates in the purchased sheet, therefore I would want it to sum them up as opposed to just give me the first line of results?

Originally Posted by benishiryo

hi jason. that would not be a lookup anymore. you can try SUMIFS
=SUMIFS(Purchased!H:H,Purchased!A:A,A2,Purchased!I:I,I2)

Brilliant!!!!!!!!!!!

Thanks vlady