# What formula do I use?

Options
✭✭

I have 2 sheets. Sheet one is a grading sheet that goes over appearance, aroma, feel ect. of a production lot. Sheet two is a stock list showing all production lots. Both sheets have the primary column set at Ample number (ID #'s). I want to use a formula in sheet two that copies the final grade from sheet one if the numbers in the primary column match.

So as you see in the pictures provided in the primary column there is a number "1220", I want to copy the "Final Grade of Product" cell from that row on sheet one and add it to the "Final Grade" cell in the row with the matching "1220" in sheet two. What formula would I use and how would I wright it?

Tags:

• ✭✭✭✭✭
Options

Hello @Nate420

You can accomplish this by using an INDEX MATCH formula:

First in Sheet 2 under "Final Grade" type =INDEX(

Click Reference Another Sheet

Reference Sheet 1

Select the column you want to display as my screen shot shows below

Click Insert Reference.

Then use the MATCH to the cell of ample #,

Then Reference Sheet 1 again, for the Ample Number column:

Final Formula Resulting

=INDEX({Sheet 1 Range 1}, MATCH([Ample #]@row, {Sheet 1 Range 2}, 0))

Let me know if this works for you!

• ✭✭✭✭✭
Options

I believe this can be done. However, there are some requirements.

You need a Column that contains the Dates of the grade.

Looking at a similar scenario to yours in a previous post it appears INDEX(COLLECT may do the trick.

You can also use a MAX formula to pull the most recent dates.

https://community.smartsheet.com/discussion/80678/looking-for-most-recent-date

I hope this helps.

• ✭✭✭✭✭
Options

Hello @Nate420

You can accomplish this by using an INDEX MATCH formula:

First in Sheet 2 under "Final Grade" type =INDEX(

Click Reference Another Sheet

Reference Sheet 1

Select the column you want to display as my screen shot shows below

Click Insert Reference.

Then use the MATCH to the cell of ample #,

Then Reference Sheet 1 again, for the Ample Number column:

Final Formula Resulting

=INDEX({Sheet 1 Range 1}, MATCH([Ample #]@row, {Sheet 1 Range 2}, 0))

Let me know if this works for you!

• ✭✭
Options

This worked great thank you very much Mr. Chris

• ✭✭
Options

@Mr. Chris Is there a way that I could add more to this formula? I have just realized that we may have multiple grades for the same ID number as we test in every stage of production. Is there a way to only match the most recent grade with the date that matches that ID number?

• ✭✭✭✭✭
Options

I believe this can be done. However, there are some requirements.

You need a Column that contains the Dates of the grade.

Looking at a similar scenario to yours in a previous post it appears INDEX(COLLECT may do the trick.

You can also use a MAX formula to pull the most recent dates.

https://community.smartsheet.com/discussion/80678/looking-for-most-recent-date

I hope this helps.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!