Index Match with Large and If Formula - Not Working

Hi,

I have a large data table of sporting events that I am trying to extract details of the top 10 events based on number of viewers.

Here is a simplified version of the formula I have tried. For the purposes of the below, I am trying to deliver the value in column A (event name) that corresponds to the largest value in column D (number of viewers) that satisfies the two if statements, B (sport) and C (date).

{=INDEX(A10:A20,MATCH(LARGE(IF(B10:B20=$A$1,IF(C10:C20=$B$,(D10:D20))),1),D10:D20,0))}

{=INDEX(A10:A20,MATCH(LARGE(IF(B10:B20=$A$1,IF(C10:C20=$B$,(D10:D20))),2),D10:D20,0))}

{=INDEX(A10:A20,MATCH(LARGE(IF(B10:B20=$A$1,IF(C10:C20=$B$,(D10:D20))),3),D10:D20,0))}

etc.

The values that are returned seem to only satisfy one of the criteria, i.e. either the sport or the data. For example, when A1=Tennis, it is still returning Football events.

Can anyone shed any light on where I am going wrong with this?

Thanks in advance

Thanks for the welcome.

Oh yes that was a typo - it is supposed to read $B$1 and B1 is a cell where a date can be inputted.

The formulas I have written above are simplifications of the actual spreadsheet I am working off as there are more like 100,000 lines of data in the table and the table is in a separate spreadsheet to where I am trying to create the top 10 list.

The formulas above seem to work okay (i'm not sure how to attach a sample workbook?), but for some reason, the same framework doesn't work correctly when I implement it on my spreadsheet, and it pulls event names out with the wrong sports or the wrong date.

For reference, here is the exact formula I am working with:

Formula:

{=INDEX('[March 2014 Reports.xlsx]Report 5'!$O$2:$O$99999,MATCH(LARGE(IF('[March 2014 Reports.xlsx]Report 5'!$C$2:$C$99999=$J$2,IF('[March 2014 Reports.xlsx]Report 5'!$J$2:$J$99999=$B$5,('[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999),"-")),1),'[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999,0))}

Formula:

{=INDEX('[March 2014 Reports.xlsx]Report 5'!$O$2:$O$99999,MATCH(LARGE(IF('[March 2014 Reports.xlsx]Report 5'!$C$2:$C$99999=$J$2,IF('[March 2014 Reports.xlsx]Report 5'!$J$2:$J$99999=$B$5,('[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999),"-")),),'[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999,0))}

etc.

Thanks

Example_Sheet.xlsx

Example spreadsheet attached - this seems to work correctly

Hi,

I have tried the above and as you say, it works fine in the sample workbook but not when I apply it to my data table (the formula I have provided 3 posts up)

Thanks

Originally Posted by richoconnor

Formula:

{=INDEX('[March 2014 Reports.xlsx]Report 5'!$O$2:$O$99999,MATCH(LARGE(IF('[March 2014 Reports.xlsx]Report 5'!$C$2:$C$99999=$J$2,IF('[March 2014 Reports.xlsx]Report 5'!$J$2:$J$99999=$B$5,('[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999),"-")),1),'[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999,0))}

Formula:

{=INDEX('[March 2014 Reports.xlsx]Report 5'!$O$2:$O$99999,MATCH(LARGE(IF('[March 2014 Reports.xlsx]Report 5'!$C$2:$C$99999=$J$2,IF('[March 2014 Reports.xlsx]Report 5'!$J$2:$J$99999=$B$5,('[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999),"-")),),'[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999,0))}

The formula above is what I am working from, and is the one that isn't working correctly. It is displaying values from column O that don't satisfy both $C$2:$C$99999=$J$2 and $J$2:$J$99999=$B$5

The formula I posted in my first post was a simplification of this and seems to work fine in the spreadsheet that I attached.

Thanks

Just to let you know I've figured this one out:

I used this formula to pull through the viewing data in order:

{=IFERROR(INDEX('[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999,MATCH(LARGE(IF('[March 2014 Reports.xlsx]Report 5'!$C$2:$C$99999=$J$2,IF('[March 2014 Reports.xlsx]Report 5'!$J$2:$J$99999=$B$5,('[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999))),1),'[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999,0)),"-")}

and then used another index match with if statements to pull through the event name:

{=INDEX('[March 2014 Reports.xlsx]Report 5'!$O$2:$O$99999,MATCH($E7,IF('[March 2014 Reports.xlsx]Report 5'!$J$2:$J$99999=$B$5,IF('[March 2014 Reports.xlsx]Report 5'!$C$2:$C$99999=$J$2,'[March 2014 Reports.xlsx]Report 5'!$R$2:$R$99999)),0))}

Turns out as there are multiple events with the same viewing data number, it was just pulling through the first event regardless of the criteria.

Thanks