Cell formula or VBA Coding

[jamesnort]

Good morning, 

I am working on an excel document that produces the rolling averages of a set of salesmen over a 3-year period. I have run into an issue to which I can not find a solution. The salesmen are broken up by the territory they work. This is identified in the current way "022/Name". The issue I am having is over the years the territories have not changed, but the salesmen have. I have one that is 01/Kyle, but was previously 01/Perdomo. I am using this Index formula (INDEX($B$53:$AJ$67,MATCH(C14,$A$53:$A$67,0),MATCH($C$1,B$52:$AJ$52,0)) to pull the information from a couple of tables, but I am receiving an error when the salesman change. I want to populate the averages by the territory number without changing the format of how they are identified.

[#2]

@jamesnort There are several ways to accomplish this.  It's hard for me to visualize the data you're working with without an example, but an index will only return one result regardless of whether there are multiple matches.  

Here is a formula which returns the average if any of the text is found in the territory/rep column.   It's the same formula in cells B2:D2 calculating the average for all of territory 1, territory1 with Kyle & territory 1 with Perdomo.    

=AVERAGE(FILTER($B$5:$I$7,ISNUMBER(SEARCH(B$1,$A$5:$A$7)),0))

Or if you want you could add another column to parse the territory (column K) using: 

=LEFT(A5,SEARCH("/",A5)-1)

 Hope this helps,

Dexter