Dynamic average_range by column text in AVERAGEIFS formula

[g3lo18]

Hi,

I am trying to obtain an average from multiple criteria using AVERAGEIFS function. The issue is I want to be able to specify the average range in a dynamic way based on column header name. I used index/match to specify the column name using header name for AVERAGEIF and it works like a charm. However, when using the same method in AVERAGEIFS it produces a #VALUE ! error. A simple example is attached. Please note there are two worksheets. Kindly advise if you have any solution to what I am trying to accomplish.

Many thanks!
g3lo18

worksheet "data example":

	A	B	C
Tony	1	2	3
Al	1	2	3
Al	1	2	3
Tony	1	2	3
Lisa	1	2	3
Lisa	1	2	3
Tony	1	2	3
Lisa	1	2	3
Tony	1	2	3

<tbody>
</tbody>

Main "Sheet2":

	AVERAGEIF works		AVERAGEIFS does not
	Tony		Tony + Lisa
A	1	=AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0)))	#VALUE !	=AVERAGEIFS(INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0)),'data example'!A:A,"Tony",'data example'!A:A,"Lisa")
B	2	=AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A4,'data example'!$A$1:$D$1,0)))	#VALUE !	=AVERAGEIFS(INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A4,'data example'!$A$1:$D$1,0)),'data example'!A:A,"Tony",'data example'!A:A,"Lisa")
C	3	=AVERAGEIF('data example'!A:A,Sheet2!$B$2,INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A5,'data example'!$A$1:$D$1,0)))	#VALUE !	=AVERAGEIFS(INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A5,'data example'!$A$1:$D$1,0)),'data example'!A:A,"Tony",'data example'!A:A,"Lisa")

<tbody>
</tbody>

Thank you!

 

[g3lo18]

The blanks are not in A1:A10 instead, they're in the actual data B2:D10.

=AVERAGE(IF(($A$2:$A$10<>"")*(NOT(ISNUMBER(SEARCH("Lisa",$A$2:$A$10))))*(NOT(ISNUMBER(SEARCH("Tony",$A$2:$A$10)))),INDEX('data example'!$A$2:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0))))