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.

Jan 5 in Others 329 views

## 1 answer to this question.

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. • 63,180 points

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE

## Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

## Excel - IF combined with Index Match

Try: =IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,Sheet2!\$C\$2:\$E\$10,3,FALSE),""),"") =IFERROR(IF(ISNUMBER(O2),INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2,Sheet2!\$C\$2:\$C\$10,0)),""),"") where the first "" means that O2 is not a number, ...READ MORE

## Index and Match multiple matches

You can try the following (formula 1) ...READ MORE

## Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE