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

0 votes

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, 2023 in Others by Kithuzzz
• 38,010 points
545 views

1 answer to this question.

0 votes

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.

enter image description here

answered Jan 6, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

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

answered Oct 8, 2022 in Others by narikkadan
• 63,700 points
720 views
0 votes
1 answer

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

answered Oct 10, 2022 in Others by narikkadan
• 63,700 points
1,052 views
0 votes
1 answer

Comparing two Excel files in R and returning different titles

Solution: library(tidyverse) dat <- read_xlsx("Book1.xlsx") dat2 <- read_xlsx("Book2.xlsx") book1_output <- anti_join(dat,dat2, ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,700 points
1,626 views
0 votes
1 answer
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
550 views
0 votes
1 answer

Index and Match multiple matches

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

answered Feb 10, 2023 in Others by narikkadan
• 63,700 points
391 views
0 votes
1 answer

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

answered Apr 6, 2023 in Others by narikkadan
• 63,700 points
523 views
0 votes
1 answer

Excel - VLOOKUP vs. INDEX/MATCH - Which is better?

Since it is much more adaptable and ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 63,700 points
309 views
0 votes
1 answer

Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,700 points
2,837 views
0 votes
1 answer

Excel formula that combines MATCH, INDEX and OFFSET

Try this: =INDEX((OFFSET(SheetA!F:F,0,SheetA!A1)),MATCH(SheetB!C4, ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,700 points
1,695 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP