Automate INDEX/MATCH or VLOOKUP the first date of the week each week.

kaia2001

I want the "Week of" column to automatically show the date of the first day in the current week each week starting with Monday as the first day, and if "Week Of" and "Project Name" both match on another sheet where the data is being enter then I want to return the next 4 columns for the rows where the "Week Of" and "Project Name" both match. I have tried so many formulas, i''m sure I am making this more difficult than it is but I am exhausted now. Please help!

WEEKLY OUTPUT

DATA:

Paul Newcome

Try something like this:

=INDEX({Column To Pull From}, MATCH([Project Name]@row, {Project Column}, 0))

kaia2001

Hi@Paul Newcome! Thank you!

2 Questions:

• Can you help with the first part? Auto populating the the current weeks first day of each week so the sheet will automatically update weekly?

"I want the "Week of" column to automatically show the date of the first day in the current week each week starting with Monday as the first day"

• The formula works to look up the project but does not take into account the date. It needs to be a 2 way match with the date & the Project matching to pull the current update.

kaia2001

For #2 I tried to use:

=IF(AND([Week Of]@row = {GSC | Project Status Week of}, [Project Name]@row = {GSC | Project Status Project}, INDEX({GSC | Project Status Project}, MATCH([Project Name]@row, {GSC | Project Status Current}, 0)), "Awaiting Update"))

It gave an error "INCORRECT ARGUMENT SET"

Paul Newcome

For number 2, it looks like the most recent dates are at the top of the sheet. The INDEX function works from the top down and stops on the first match which means that you wouldn't need to incorporate the date.

If you do need to incorporate the date, I will have to get back to you on how exactly to generate the date but then we would switch over to an INDEX/COLLECT.