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

DDYC 0
Viewed 55

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?

Data sheet with headers

Formula located at bottom

Data sheet exercise maxes

Headers named range

data named range

average google-sheets-formula match google-sheets
1 Answers

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.

LASheets0
Related