Is there a formula that will search through multiple columns based on a search key?

Question

The current formula is:

=averageifs(index(data,,match(B5,Headers,)),index(data,,match("position 1",Headers,)),B3)

The function averages total weight for specific positions. It goes through my data sheet's headers to find the column titled "position 1" to find all the athlete who play position B3, but I have athlete who play multiple positions and want to have them included as well.

Is it possible to get it to search through multiple column to find all the athletes who play the same position?

Headers named range

data named range

Answer 1

This gets a bit complex, but I think it will give you what you need. It does change the formula from the AVERAGEIFS to the QUERY function.

=QUERY(data,"select avg("&LEFT(ADDRESS(Headers,MATCH(B5,Headers,0),4),1)&") where C='"&B3&"' or D='"&B3&"' or E='"&B3&"' or F='"&B3&"' or G='"&B3&"' or H='"&B3&"' or I='"&B3&"' or J='"&B3&"' or K='"&B3&"' or L='"&B3&"' or M='"&B3&"' label avg("&LEFT(ADDRESS(Headers,MATCH(B5,Headers,0),4),1)&") ''")

I believe the only thing you may need to change is deleting some of the comparisons to columns. I wasn't quite sure how many Position columns you have in your original sheet.