Finding task with highest date

Question

I have this sheet where I want to do simple project planning and resource booking

The columns 6-17 show week numbers and each main objective within a project has a start week and end week - no actual dates needed in this case

So I set the start/end week numbers and based on that either X or Y is set into the cell, I'll use that for conditional formatting later to display a "Gantt" like view

Now the problem I am facing is with the resources further down the page.

How do I find the objective Jane is booked for that has the highest week number?

Resources can be part of several objectives and what I want to display in the bottom is, when will Jane be available again for new projects?

Answer 1

Suppose TEAM is in B2:B10, to find if member Alice is part of the team, you do regexmatch(B2:B10,"\b(?i)Alice\b"). Here, \b(?i)Alice\b is a regular expression. \b means word boundary, and (?i) means the word search is not case sensitive.

Now, suppose you want to all A2:A10 values where Alice is a part, you do filter(A2:A10, regexmatch(B2:B10,"\b(?i)Alice\b"))

Next, to get the maximum A value, you do max(filter(A2:A10, regexmatch(B2:B10,"\b(?i)Alice\b")))

See filter and regmatch