INDEX MATCH formula in Excel returning incorrect results in some cells and correct results in others

Question

Before I continue, let me clarify that I cannot upload the actual spreadsheet because it contains sensitive information.

I use a spreadsheet to track information from sporting events and determine the House and age champions.

The spreadsheet's columns F, K, L, and N are for gender, name, age, and score. To create champions in each age and gender category, I use the algorithm below (eg 12-year-old female, 12-year-old male, 13-year-old female, etc)

=INDEX($K$2:$K$1000,MATCH(MAXIFS($N$2:$N$1000,$F$2:$F$1000,"F",$L$2:$L$1000,"12"),$N$2:$N$1000,0))

where the "12" changes to the various age groups and the "F" becomes an "M."

The formula gives the right answers for "F" "12," "16," and "17," but the wrong answers for "F" "13," "14," and "15." And for the men's age champions, a similar mix. The inaccurate result's "name" is the wrong age and gender.

I've tried a lot of different "solutions," such as text/number substitutes and various formulas, but the wrong outcome keeps happening.

I would be very grateful for any assistance.

Answer 1

Step through the formula that produces the "incorrect" results using the Formula Evaluation tool on the Formulas ribbon.

You will observe the resolution of MAXIFS. You'll observe how MATCH resolves.

Since the number produced by MAXIFS is probably not a unique value, the MATCH function looks for the first match, which can be for the wrong gender and age group.

You get access to the new Dynamic Array features if you use Office 365. Then, you may utilise

=INDEX(FILTER(K2:K10,(F2:F10="m")*(L2:L10=13)),MATCH(MAX(FILTER(N2:N10,(F2:F10="m")*(L2:L10=13))),FILTER(N2:N10,(F2:F10="m")*(L2:L10=13)),0))

The formula is demonstrated in use in the screenshot. Your formula is displayed in Cell Q2 and it produces the incorrect value "girl4", which matches the output of MAXIFS.

The formula in Cell Q3 uses Filter, which uses the same filter on the match as it does on the MAX function.