AverageIf with Vlookup and Concatenate

[DoggoMan]

Hey guys,

I am dealing with 10'000 plus lines and need to get a rolling 12 month average for items on another sheet, however I cannot interact with sheet2 due to the data overwrighting previous data daily. I am trying to make another sheet so it looks pretty.
With the averages I am trying to use the month, item and company to lookup using the pre made CAT that comes with the report sheet, however I cannot get the averages with AVERAGEIF since there are some items that are the same or similar and don't seem to work with what I am trying to do.

I have tried =AVERAGE(IF(ISNUMBER(MATCH(C:C,IF('Monthly Sale Averages cat'!E:E=C4,'Monthly Sale Averages cat'!G:G),0)),'Monthly Sale Averages cat'!G:G))
and
=AVERAGEIFS('Monthly Sale Averages cat'!G:G,'Monthly Sale Averages cat'!E:E,(CONCATENATE(D5,A7,B7))),0)
and
=AVERAGEIFS(INDEX('Monthly Sale Averages cat'!E:G,0,MATCH(CONCATENATE(C$2,A3256,B3256),'Monthly Sale Averages cat'!E3254:G3254,0)),'Monthly Sale Averages cat'!E:E=CONCATENATE($C$2,A3259,B3259))


but it doesn't appear to work.

any help appreciated!

 

[Toadstool]

Hi DoggoMan,

Would wildcards work for you?

DoggoMan.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Cat	Hashes	Sales			Business	Item Number	January	February	March	April	May
3	December Heyworks1025L		999			Heyworks	1025L	6
4	January Heyworks1025L		4			Heyworks	2000L	7
5	January Silver Suplys1025L		5			Talkworld	1025L	2.5	24	74	111
6	January Heyworks1025L		8			Silver Suplys	1025L	5
7	January Heyworks2000L		7
8	January Talkworld1025L		2
9	January Talkworld1025L		3
10	February Talkworld1025L		2
11	February Talkworld1025L		33
12	February Talkworld1025L		37
13	February Talkworld2000L		37
14	March Talkworld1025L		74
15	April Talkworld1025L		111
Sheet1

Cell Formulas
Range	Formula
I2:L2	I2	=EOMONTH(H2,0)+1
H3:L9	H3	=IF($F3="","",IFERROR(AVERAGEIFS($C$3:$C$22,$A$3:$A$22,"*"&$F3&"*",$A$3:$A$22,"*"&$G3,$A$3:$A$22,TEXT(H$2,"mmmm")&"*"),""))