VLOOKUP/INDEX/IF Help?

GD1210

Hello,

I'm attempting to integrate data between a "Master" sheet and a "Daily Entry" form. I'm trying to populate the "Quantity Completed" with the value in the "Parts Run" cell after successfully matching the Part Numbers in both sheets, circled in red. In a perfect world, an employee would enter their daily work report and press submit. Upon submission, the "Master" sheet would recognize the part number, look up the "Parts Run" cell for the corresponding Part Number, and add it to the Quantity Completed column cell.

So far, I've tried the following:

=INDEX({Daily Entry Range 4}, MATCH([Part Number]@row, {Daily Entry Range 3}, 0))

This returns a "NO MATCH"

I also tried:

=IF([Part Number]@row = {Daily Entry Range 1}, {Daily Entry Range 2})

This works partially, but only draws the "Parts Ran" data from a specific cell vs. from the cell corresponding the part number row.

Any help is greatly appreciated, this will be a terrific asset to assist with daily operational management. Thanks!

Brian_Richardson

I'm assuming here that the Daily Entry could include any part number, and someone could enter the same part number on multiple days. If so then you don't want to use an INDEX/MATCH, you want a SUMIF.

Make this formula a column formula for the Quantity Completed column in your Master sheet.

=SUMIF( {Part Number} , [Part Number]@row , {Parts Ran} )

{Part Number} is a cross-sheet reference to the entire Part Number column in your Daily Entry Sheet

{Parts Ran} is a cross-sheet reference to the entire Parts Ran column in your Daily Entry Sheet

Mark.poole

Is each part number Unique in the range? OR can the same part number appear multiple times through out the column?

GD1210

Hello Mark,

Each Part Number will be unique and will not appear multiple times.

Thanks!

Brian_Richardson

I'm assuming here that the Daily Entry could include any part number, and someone could enter the same part number on multiple days. If so then you don't want to use an INDEX/MATCH, you want a SUMIF.

Make this formula a column formula for the Quantity Completed column in your Master sheet.

=SUMIF( {Part Number} , [Part Number]@row , {Parts Ran} )

{Part Number} is a cross-sheet reference to the entire Part Number column in your Daily Entry Sheet

{Parts Ran} is a cross-sheet reference to the entire Parts Ran column in your Daily Entry Sheet

GD1210

Thank you! That worked great! I'm going to eventually replace the "Part Number" dependency with a unique job number, instead, to avoid accidentally summing up old part numbers from closed out job numbers but now I know the structure. Thanks again!