What formula do I use?

Nate420 · 09/08/22

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 · 09/08/22

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 · 09/09/22

https://community.smartsheet.com/discussion/comment/343041#Comment_343041

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 · 09/08/22