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 in Others by Kithuzzz
• 29,080 points
36 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 by narikkadan
• 53,920 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
• 53,920 points
164 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
• 53,920 points
208 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
• 53,920 points
275 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
• 53,920 points
93 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
• 53,920 points
63 views
0 votes
1 answer

Find the serial number with criteria

Use the filter function: =FILTER($C$11:$C$39;($H$11:$H$39=MAXIFS($H$11:$H$39;$N$11:$N$39;4))*($N$11:$N$39=4)) Maybe you have to ...READ MORE

answered Jan 10 in Others by narikkadan
• 53,920 points
53 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
215 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
• 53,920 points
475 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
• 53,920 points
181 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