Need a lookup for subtotaled data

[lexident]

Every month I need to reconcile the deposits we receive daily for our company. These can range from 50 to hundreds of deposits a day, every weekday of the month. My reports consist of a summary sheet and the data sheets.

The summary sheet is logged horizontally by date (always a 31 day month and we hide the weekends once complete) so our dates range from (ex.) 6/1/13 (C5) to 6/31/13 (AG5) - this is our header, so the actual deposit values are in cells C6-AG6. These stay constant with every month.

The data sheets are sorted by deposit date, and then subtotaled (totals land in Column G). As of now, to pull the totals from the data sheet to the summary, I use simply (ex.) in cell C6 =G45 for the total for 6/3/13 deposits. What I’m finding difficult is that when I run the report for July, 7/3/13 total deposits will not be in G45 on the data sheet.

Every month the dates in the header for the summary sheet are changed to fit the month, but they stay in the same cell, as stated before. I need a function/lookup/macro, whatever I can find that will search the total for that specific date on the data sheet and pull the total value to the summary sheet in row 6. I’m not sure how to do this without using the exact cell.

I tried =VLOOKUP(E5,Sheet10!A2:G2000,7,0) hoping it would search the date and pull the total, but it keeps giving me an error. The date is the exact same format on the summary sheet as it is on the data sheet as well. (If this does not have to be the case that would be even better).

Any thoughts?

[lexident]

I was able to solve this using INDEX/MATCH


=INDEX(Sheet10!$G1:$G1999,MATCH(B5,Sheet10!$D1:$D1999,0))