Nested Vlookup or alternative?

[scoobydoo99]

Hi,

Newbie here so take it easy.

I have a workbook with 2 worksheets containing two tables of data. The
first two columns in each sheet contain a name and a date and the third
column contains data I'm trying to compare.
The first worksheet is weekly totals with a date for the end of the
week. The second worksheet contains daily figures and I'd like to match
the names from the first columns then sum the daily totals and compare
against the weekly total.

Hope this makes sense - I thought a nested vlookup including a sum for
the daily figures would do but can't get this to work.

Any thoughts, suggestions welcome.

 

[Grandslam90]

Hey Sccoby,

There's lots of different ways to do what you're asking but here's one
that might work for you.

I have called your weekly data sheet "weekly" and the daily data sheet
"daily" - great naming conventions huh ?

Column A in both has name, Column B Dates (as date numbers !) and
Column C has amounts.

The first row of actual data occurs in row 2.

In worksheet "Weekly" in cell E2 i have the following formula.

=SUMPRODUCT((Daily!$A$2:$A$16=Weekly!A2)*(Daily!$B$2:$B$16<=Weekly!B2)*(Daily!$B$2:$B$16>=Weekly!B2-6)*(Daily!$C$2:$C$16))

I have atached a little zip file, at least I hope I have

Mike


+-------------------------------------------------------------------+
|Filename: Nested Vlookup or alternative.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3957 |
+-------------------------------------------------------------------+

 

[scoobydoo99]

Many thanks Grandslam, worked a treat - can't believe how quickly you
responded.