How to use formula to capture and match item from a range of data?

I have this sheet that will record the various documents checklist for each client. The Client ID may repeat.

Whenever Doc 2 is completed, a staff will record the date in the Send Out Date.

We have Sheet 2 that we would like to pull this Send Out Date when the range between Task 1 - Task 4 display "Doc 2", and the client ID matches.

Is there anyway we can write a formula to capture the send out date from Sheet 1, when the following criteria matches:

1) Client ID

2) Task 1 - Task 4 display "Doc 2"

Appreciate assistance in working out this formula.

Find more posts tagged with

Accepted answers

Paul Newcome

You would need a helper column on the first sheet that captures when "Doc 2" is entered. From there you would use an INDEX/COLLECT on the second sheet.

First Sheet helper column (text/number):

=IF(COUNTIFS([Task 1]@row:[Task 4]@row, @cell = "Doc 2")> 0, "Doc 2")

Second Sheet formula:

=INDEX(COLLECT({Date Column}, {Client ID Column}, @cell = [client ID]@row, {Helper Column}, @cell = "Doc 2"), 1)

All comments

Paul Newcome

You would need a helper column on the first sheet that captures when "Doc 2" is entered. From there you would use an INDEX/COLLECT on the second sheet.

First Sheet helper column (text/number):

=IF(COUNTIFS([Task 1]@row:[Task 4]@row, @cell = "Doc 2")> 0, "Doc 2")

Second Sheet formula:

=INDEX(COLLECT({Date Column}, {Client ID Column}, @cell = [client ID]@row, {Helper Column}, @cell = "Doc 2"), 1)

Vivien Chong

Thanks @Paul Newcome. Adding the Helper Column is a great help indeed.

Paul Newcome

Happy to help. 👍️

I honestly can't think of a way to do it without the helper column because you have different range sizes.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.