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.
Best Answer
-
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)
Answers
-
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)
-
Thanks @Paul Newcome. Adding the Helper Column is a great help indeed.
-
Happy to help. 👍️
I honestly can't think of a way to do it without the helper column because you have different range sizes.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!