Excel formula, INDEX MATCH MAX IF

0
votes

I have a data in 3 columns & 17 Rows (F8:H35) Column Headers are: F= Offense score / G= Defensive score / H= Team Name

I want to show the team name from (H) with the Max Difference between Offense & Defense (F-G)

I was able to find the Max Difference with this formula:

=MAX(IF((F8:F35<>"")*(G8:G35<>""),F8:F35-G8:G35))

But how do I show the team name (H) ? I tried: Index Match Max If but I'm getting too few arguments

Not Working:

=INDEX(H8:H35,MATCH(MAX(IF((F8:F35<>"")*(G8:G35<>""),F8:F35-G8:G35))*F8:H35))

I'm in over my head here, not sure if I'm close or really far off. Any and all help is greatly appreciated. It's worth noting that this is a filter list, the row order changes, base on offensive score (F).

1
excelexcel-formula
2 questions: 1) are you looking for maximum positive difference between F and G, or just maximum difference (either positive or negative) between F and G? 2) are the team names unique, and what would you like to return if there are more than one team have the maximum difference? – Terry W

1 Answers

0
votes

This array formula appears to work

=INDEX(H8:H35,MATCH(MAX(IF((F8:F35<>"")*(G8:G35<>""),F8:F35-G8:G35)),F8:F35-G8:G35,0))

Would be easier though if you added a helper column showing the difference.