What formula do I use?

Nate420

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?

=Chris Palmer

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!

=Chris Palmer

Nate420

Sep 9, 2022

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

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.

=Chris Palmer

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!

Nate420

This worked great thank you very much Mr. Chris

Nate420

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

=Chris Palmer

Nate420

Sep 9, 2022

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

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.