Bring Data From Another Sheet (INDEX, MAX, COLLECT, MATCH)


Hello Community,

I need to bring data from one sheet to another. I usually accomplish this using INDEX, MATCH combination.

My challenge right now is the following: Sheet 1 has unique records. Sheet 2 references Sheet 1 on a unique identifier (taskid), but there might be multiple records in Sheet 2 for the same taskid. I need to bring the most recent record from Sheet 2 into Sheet 1 and link it as a Comment column to the unique record in Sheet 1.

Here is a sample

I anticipate it must be something like INDEX, MAX(COLLECT(....))) but cannot wrap my head around it.

Can you help me, please?



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Aurus,

    Here is a sample formula structure. Note, you'll need to adapt it to your specific column names and sheet names:

    =INDEX({Sheet2 Comment Range}, MATCH(MAX(COLLECT({Sheet2 Date Range}, {Sheet2 Task ID Range},
    [Task ID]@row)), COLLECT({Sheet2 Date Range}, {Sheet2 Task ID Range}, [Task ID]@row), 0))
    • {Sheet2 Comment Range}: The range of cells containing comments in Sheet 2.
    • {Sheet2 Date Range}: The range of cells containing dates in Sheet 2.
    • {Sheet2 Task ID Range}: The range of cells containing Task IDs in Sheet 2.
    • [Task ID]@row: The Task ID in the current row of Sheet 1 you're referencing.

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!