I have two tables on separate sheets. One table needs to return the value of the product purchased for a specific date. I am trying to use the formula =INDEX(Sheet1!$a$2:$d$5,MATCH(B2,Sheet1!$b$2:$b$5,0),2) but I think it is only a criteri for the customer and none for the date. Can you help me with this? Thanks.
<tbody>
</tbody>
<tbody>
</tbody>
A | B | C | D | |
1 | Customer | Product | Start Date | End Date |
2 | A | Shampoo | 8/1/2015 | 8/3/2015 |
3 | B | soap | 8/1/2015 | 8/3/2015 |
4 | C | Towel | 8/1/2015 | 8/30/2015 |
5 | A | soap | 8/7/2015 | 8/31/2015 |
6 | B | shampoo | 8/15/2015 | 8/31/2015 |
Sheet1 |
<tbody>
</tbody>
A | B | C | D | |
1 | Date | Customer | Total | Product |
2 | 2-Aug-15 | A | 5 | =INDEX(Sheet1!$a$2:$d$5,MATCH(B2,Sheet1!$b$2:$b$5,0),2) |
3 | 1-Aug-15 | B | 3 | |
4 | 20-Aug-15 | C | 6 | |
5 | 10-Aug-15 | A | 8 | |
6 | 19-Aug-15 | B | 2 | |
Sheet2 |
<tbody>
</tbody>