Index/Match with Has and Specific Select

Gladys Liley

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

Paul Newcome

Give this a try:

=INDEX(COLLECT({Column To Pull}, {Branch}, HAS(@cell, [Branch Name]@row), {Date Range}, @cell = DATE(2024, 11, 7)), 1)

Paul Newcome

Are you able to provide some screenshots?

Gladys Liley

Top is master sheet and bottom image is weekly sheet.

jessica.smith

@Gladys Liley

Try an Index/Collect formula instead:

=INDEX(COLLECT({Source_Week}, {Source_Branch}, CONTAINS(Branch@row, @cell)), 1)

Does that work for you?

Paul Newcome

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)

Gladys Liley

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.

Paul Newcome

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?

Gladys Liley

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.

Gladys Liley

@Paul Newcome

Please see above and screehshot.

Paul Newcome

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?

Gladys Liley

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.

Paul Newcome

Ok. And how exactly is the [Week Ending Date] column in the source sheet populated, and exactly what column type is it?

Gladys Liley

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.

Paul Newcome

Give this a try:

=INDEX(COLLECT({Column To Pull}, {Branch}, HAS(@cell, [Branch Name]@row), {Date Range}, @cell = DATE(2024, 11, 7)), 1)

Gladys Liley

Worked perfectly!!!😁 thank you @Paul Newcome you're my formula guru!!!

Paul Newcome

Happy to help. 👍️