VLOOKUP/INDEX/IF Help?

Options

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!

Tags:

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓
    Options

    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

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • GD1210
    Options

    Hello Mark,

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

    Thanks!

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓
    Options

    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

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • GD1210
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!