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.

Attached Files

Example.xlsx (104.6 KB, 6 views)

Cross post MAX INDEX MATCH with criteria
Add criteria to MAX INDEX MATCH formula | MrExcel Message Board

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Apologies for not including the cross-post links. This is now solved in the Excel Forum thread. Thanks. MAX INDEX MATCH with criteria [SOLVED]