Matching cell values with a partial search AND returning first non-zero value?

Question

Having issues trying to set up the right formula to find the first instance of a partial match that has an associated non-zero value. Basically, I have a project that logs estimated and actual hours worked by role. If a role has no estimated hours but does have actual hours, it needs to be rolled up into the lines with actuals OR display in a separate line under the role variation that does have actual hours.

Projects can include estimates for any level of seniority within roles. However, if there are only estimates for one level of seniority but actuals on a different level of seniority, the estimates must roll up to display under the level of seniority that has actuals. Seniority levels between roles (where the role is "ROLE") include:

• ROLE Associate
• ROLE snr.
• ROLE jnr.

The reference tab is formatted like:

Name	Role	Estimated Hours Needed	Actual Hours Worked
Director	Amanda	0	20
Director	Bob	0	25
Director Associate	n/a	50	0
Secretary	Kathy	0	30
Secretary jnr	n/a	40	0

But, I need to collapse the rows since no "Director Associate" or "Secretary jnr" worked on the project; those estimated hours instead need to be put on Amanda's and Cathy's lines since they were the ones who worked on the job. The main tab needs to be formatted like:

Name	Role	Estimated Hours Needed	Actual Hours Worked
Director	Amanda	50	20
Director	Bob	50	25
Secretary	Cathy	40	30

I think also acceptable would be:

Name	Role	Estimated Hours Needed	Actual Hours Worked
Director	n/a	50	0
Director	Amanda	0	20
Director	Bob	0	25
Secretary	n/a	40	0
Secretary	Cathy	0	30

Easy enough to get names and roles but I cannot figure out how to do a partial search of the title PLUS the first non-zero value. In Amanda's case, I need to do a partial match search for "Director" and pull the first non-zero value for roles that include "Director" (in this case, it would be 50 hours under "Director Associate"). I tried and index/match with &"*" but the problem is "Director" doesn't have any additional characters after it, so it wouldn't be helpful in a use case where "Director" has the first non-zero value.

Including an example sheet here with some notes on what I've tried and found: https://docs.google.com/spreadsheets/d/1M0DO0lh3V1dDHcSBCZlE4ty7Y2TWJ0zLKWGlQSztHa0/edit#gid=0

Summary of notes:

• Adding a &"*" at the end of my search doesn't work since the mid-tier seniority titles don't have additional strings after (ie, Art Director vs. Art Director jnr)
• I'm unable to collapse roles with VLOOKUP but I set it up in the sheet anyway since it might help with referencing (I'm not as comfortable with index/match but it seems like it might be the way to go)
• Creating a pivot table gets me the closest to what I'm looking for, except I can't collapse the roles, which is key

I came across this formula =INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0)) in my research but I'm not sure how to apply it to the dataset I have since I need to be able to reference another sheet.

Answer 1

Use vlookup() to replace associates, juniors and seniors with N/A in the Resource column with the first non-N/A resource that has a Role that matches the role, and query() to aggregate the results, like this:

=arrayformula( query( 
  { 
    if( 
      B1:B = "N/A", 
      iferror( 
        vlookup( 
          regexextract(A1:A, "(.+) (?:(?i)Associate|jnr|snr)"), 
          A1:B, { 1, 2 }, false 
        ), 
        A1:B 
      ), 
      A1:B 
    ), 
    C1:F 
  }, 
  "select Col1, Col2, sum(Col3), sum(Col4) 
   where Col1 is not null 
   and Col1 <> 'Total:' 
   group by Col1, Col2", 
  1 
) )

If there are just a few such cases, you can hard code those exceptions, like this:

=arrayformula( query( 
  { 
    if( A1:A = "Director Associate", { "Director", "Amanda" }, 
    if( A1:A = "Secretary jnr", { "Secretary", "Kathy" }, 
    A1:B ) ), 
    C1:D 
  }, 
  "select Col1, Col2, sum(Col3), sum(Col4) 
   where Col1 is not null 
   group by Col1, Col2", 
  1 
) )