Index/Match with Has and Specific Select
I have an master roster and each week I need to ensure a response for each. Since each responder has mutliple areas of responsibility, one submission may cover multiple items from the master roster.
I used an index/match formula however when there are multiple entries in a single line, I get a #No Match. And now that the sheet has a secondary week of data, it can't distinguish the difference between last week and this week.
=INDEX({source sheet}, MATCH([ItemA]@row, {source sheet}, 0)
Any guidance is appreciated
Best Answer
-
Give this a try:
=INDEX(COLLECT({Column To Pull}, {Branch}, HAS(@cell, [Branch Name]@row), {Date Range}, @cell = DATE(2024, 11, 7)), 1)
Answers
-
Are you able to provide some screenshots?
-
Top is master sheet and bottom image is weekly sheet.
-
Try an Index/Collect formula instead:
=INDEX(COLLECT({Source_Week}, {Source_Branch}, CONTAINS(Branch@row, @cell)), 1)
Does that work for you?
-
I would recommend using an INDEX/COLLECT so you can incorporate the HAS function.
=INDEX(COLLECT({Column To Pull}, {Branch}, HAS(@cell, [Branch Name]@row)), 1)
-
I used HAS in the formula. The next question is adding the date criteria so it only pulls either 10/31/2024 or 11/07/2024.
-
I'm not sure I follow that last bit. In your screenshots, Back Bay has pulled 10/31/24 in the top sheet and it has both dates in the bottom sheet. How exactly are you deciding which one to pull?
-
The Master Tracker is designed to track responses each week that are submitted via a form into the Weekly sheet. I tried adding DATE(2024,10,31) into the formula however the Branch name is not a date and it errored.
-
Please see above and screehshot.
-
So you just want the date 11/7/2024 in the [WK 11/7/2024] on the row with Albany in it if the other sheet has a row with Albany and that date in it? Then you want to do the same with the other date columns?
-
Thank you @Paul Newcome!
Yes, that's correct.
The Weekly sheet will receive weekly confirmations in the [Branch Name] and [Week Ending Date]. By the end of the year, Albany will have 52 entries in the weekly sheet (branch name with corresponding week ending date).
The Master sheet will have the single [Branch Name] and 52 columns representing each week ( [WK 10/30/2024], [WK 11/7/2024], [WK 11/14/2024], etc.) with one Master sheet per year. The formula for each week column needs to be unique to only pull in the response. If no response is listed, then I want to say "No Response." which will trigger a follow-up.
-
Ok. And how exactly is the [Week Ending Date] column in the source sheet populated, and exactly what column type is it?
-
The source sheet is populated on a weekly basis through a form. Source sheet sends weekly email with a link to the form to be filled out. The [Week Ending Date] column is a date column.
-
Give this a try:
=INDEX(COLLECT({Column To Pull}, {Branch}, HAS(@cell, [Branch Name]@row), {Date Range}, @cell = DATE(2024, 11, 7)), 1)
-
Worked perfectly!!!😁 thank you @Paul Newcome you're my formula guru!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!