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?

Nick

Find more posts tagged with

Support

References

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.