help with index/match or lookup

[zzblitzz]

Ello!

Need help with this problem

i got 2 sheets, and i need to get the grnd total from sheet 2. How can i lookup/search on sheet two using the date and name on sheet 1.

attach is a sample(name on sheet 1 not always 4) really need help thnks

[daffodil11]

SUMPRODUCT

Paste this in L2 and then copy downwards:

Please Login or Register  to view this content.

Sumproduct works best for looking up the combination of arrays where the end result is a number. For your data on Sheet1, L2, we are searching Sheet2A2:A9 for rows matching Sheet1A2, then for those only those matches we are searching Sheet2B2:B9 for rows matching B2, and for everything that's still a match we are adding up their corresponding rows for the Column S2:S9.

In your example, there's only 1 thing to add up, but it still works fine.

If you had 50 rows out of 1000 that matched both the date and name, it would sum their results instead.

[zzblitzz]

I'm sorry, what i mean is get the value of the cell in sheet 2 grand total.

ex. im at sheet 1 i'll do a search using the date and name on sheet 1 to sheet two and get the value in the grand total column. like 8/1/2013 and cust A. then get the value on sheet 2 which is 4

[daffodil11]

Yes, that's what I did. You're adding up all the matches on Sheet2 that correspond to date and name.

Each combination only results in one match. One plus nothing, equals the match.


In my first response I did not see your column out in N. Just plop the formula into N2 instead of L2. It works the same regardless.

[daffodil11]

Here's your finished example, with explanation.

Attachment 261758