Forum Discussion
Need help with sumifs, vlookup, etc type question
I am on Excel 2010. I have two sets of data from two different systems and I'm trying to audit the data output. Each has a date, a part number, and a quantity. They are on different sheets of the sa...
In D2 on Sheet1:
=IFERROR(INDEX('Sheet II'!$C$2:$C$1000, MATCH(1, ('Sheet II'$A$2:$A$1000=A2)*('Sheet II'!$B$2:$B$1000=B2), 0)), "")
Change Sheet II to the actual name of the second sheet, and adjust the ranges if your data extend below row 1000.
Confirm the formula by pressing Ctrl+Shift+Enter, then fill down.
- This doesn't seem to be working for me - and I think it's because there is no 1:1 quantity.
The data from one source many have multiple entries (no idea why) and looks like this:
Sheet I date, part #, quantity = 10
Sheet II same date, same part #, quantity = 5
same date, same part #, quantity = 2
same date, same part #, quantity = 3
Which sounds like a sumif(s) type function would be needed...?=SUMPRODUCT('Sheet II'!$C$2:$C$1000, ('Sheet II'$A$2:$A$1000=A2)*('Sheet II'!$B$2:$B$1000=B2))
