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

Aurus

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?

Nick

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.