# Need help with sumifs, vlookup, etc type question

Copper Contributor

# 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 same workbook (data dump from respective ERP systems).

I want to bring quantities from Sheet II over to Sheet I, as long as they meet the same date and part number as Sheet I. The final would look like this:

Date  /    Part#    /    Quantity    /     Sheet II quantity

There are thousands of part numbers, with rev changes and new part numbers all the time, so setting them as a, "product" to reference doesn't make sense as that would have to be maintained.

How would I go about doing this?

3 Replies

# Re: Need help with sumifs, vlookup, etc type question

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.

# Re: Need help with sumifs, vlookup, etc type question

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...?

# Re: Need help with sumifs, vlookup, etc type question

=SUMPRODUCT('Sheet II'!\$C\$2:\$C\$1000, ('Sheet II'\$A\$2:\$A\$1000=A2)*('Sheet II'!\$B\$2:\$B\$1000=B2))