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":

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Al[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Al[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Lisa[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Lisa[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Lisa[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
</tbody>[/TABLE]

Main "Sheet2":

[TABLE="width: 2161"]
<tbody>[TR]
[TD][/TD]
[TD]AVERAGEIF works[/TD]
[TD][/TD]
[TD]AVERAGEIFS does not[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tony[/TD]
[TD][/TD]
[TD]Tony + Lisa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD]=AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0)))[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD]=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")[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD]=AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A4,'data example'!$A$1:$D$1,0)))[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD]=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")[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[TD]=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)))[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD]=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")[/TD]
[/TR]
</tbody>[/TABLE]


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))))