Add criteria to MAX INDEX MATCH formula

[ollyhughes1982]

Hi all,

I have the below formula, but I need to add in one IF criteria to it. The formula basically lists if I have done a run of 7 different countries in consecutive parkrun weeks - which is working correctly. However, I now want to add in a criterion to ignore one particular country value - 'UK Crown Dependencies'.

=IF(ROW($A1)<=MAX('All Completed Runs'!$CQ:$CQ),INDEX('All Completed Runs'!D:D,MATCH(MAX('All Completed Runs'!$CQ:$CQ),'All Completed Runs'!$CQ:$CQ,FALSE)-MAX('All Completed Runs'!$CQ:$CQ)+ROW($A1)),"")

I need to only include values from 'All Completed Runs'!D:D which are not equal to ‘UK Crown Dependencies’. i.e. Ignore any country values that equal ‘UK Crown Dependencies’. This is because it is not a true country and needs to be ignored. In other formulas I have added in something like below, but am struggling to do this in this case:

'All Completed Runs'!D3:D5002<>"UK Crown Dependencies"

Thanks in advance,

Olly.

 

[ollyhughes1982]

Here is a link to the example: Example.xlsx
Thanks.

 

[ollyhughes1982]

Really stuck on this one, have also posted on Excel Forum: MAX INDEX MATCH with criteria

I wonder if some sort of 365 FILTER function, combined with SEQUENCE might be able to achieve what I am after.

Thanks.

 

[Peter_SSs]

have also posted on Excel Forum: MAX INDEX MATCH with criteria

Please provide links to all cross-posts, always.

 

[ollyhughes1982]

Please provide links to all cross-posts, always.

Apologies, I thought I already had. MAX INDEX MATCH with criteria

 

[RoryA]

Peter's point is that that is not the only other forum you posted this on.

 

[Peter_SSs]

Apologies, I thought I already had.

You had already given us a link to that thread. I was talking the other one(s).

 

[ollyhughes1982]

You had already given us a link to that thread. I was talking the other one(s).

Apologies. Add criteria to MAX INDEX MATCH

 

[Peter_SSs]

Thank you.

 

[ollyhughes1982]

This is now solved in the Excel Forum thread. Thanks, all. MAX INDEX MATCH with criteria