Index Match Whole Cell Mult Criteria

[bpmurphy]

Hey all, we're banging our heads against the wall on this formula - every time we think we get it, it doesn't work properly in testing. Essentially we have two tabs, a reference (Team List) tab where there's a Team Name (column A), a Process Type (column B), and an Ownership Level (column C). One process type can be owned by multiple teams but we only want to display the primary owner team which would be marked as a 2 for ownership level.

Current formula that we include on the data tab
=IFERROR(IF(SUMIFS('Team List'!C:C,'Team List'!A:A,E2,'Team List'!B:B,C2)>0,"",INDEX('Team List'!A:C,MATCH(C2,'Team List'!$B:$B,0),MATCH(2,'Team List'!$C$2:$C$5000,0))),"Unknown")

In our data tab - we have raw data pulls from our database and what we're doing is seeing if the process type that's open for the current owner is matching what we have for ownership. If it doesn't match, it should show the primary owner - if it does match, it's just a blank. The problem we're running into is that it's not looking at the whole team name, only the first word. For instance, we have Cash Team A, Cash Team B, & Cash Team C - Cash Team A is primary and has a 2 in the ownership column. The formula is ignoring which is a 2 and which isn't and just providing the first team that starts with "Cash" in the list for that process type. In this case, it's showing Cash Team C because they're first in the list. Any way to get this to properly search for a 2 in the ownership column AND the full name of the team rather than just the first word? Any help would be huge as we've been at this for a while.

 

[Eric W]

Try:

=IFERROR(IF(SUMIFS('Team List'!C:C,'Team List'!A:A,E2,'Team List'!B:B,C2)>0,"",INDEX('Team List'!A:A,MATCH(C2&"|2",'Team List'!$B:$B&"|"&'Team List'!$C:$C,0))),"Unknown")

and confirm the formula with Control+Shift+Enter.

 

[bpmurphy]

Thank you for the response, it does work with that formula. However, I forgot to mention a key point - I need to do this without arrays because I have ~22,000 rows of data. Due to a series of events, our reports post with the formulas intact via an automation and get auto posted to SharePoint so when a user goes into the workbook - they would be met with massive Excel calculations for the array formula.

 

[Eric W]

Well, you could certainly improve performance by changing the whole column references (B:B) to a smaller range reference(B2:B22000), but that only helps so much. I'm afraid I don't know any non-array ways to accomplish this. There are some formulas which don't require CSE, but behind the scenes, they're still array formulas. The only other thing I can of is to have an event handler that monitors columns C and E, and when something changes do the lookup and place the result in the right cell. But I don't know if that would work with your setup, or if you can have VBA at all.