Find records containing some characters but not others

[ollyhughes1982]

Hi all,

I have the following formula, which currently lets me find the first record containing ‘Queen’.

=IFERROR(XLOOKUP("*"&"Queen"&"*",'All Completed Runs'!$C$4:$C$2003,'All Completed Runs'!$C$4:$C$2003,,2),"")

Is there a way that I could amend this formula so that instead of finding the first record beginning containing ‘Queen’, it identifies the first 50 that include an ‘I’, but not a ‘V’, ‘X’, ‘L’, ‘C’ or ‘M’?

It’s a Roman numerals based parkrun challenge, where the following rules apply:

• Level 1 requires 1 event containing the letter 'I', but none of the other 5 category letters
• Level 5 requires 5 different events containing the letter 'V', but none of the other 5 category letters
• Level 10 requires 10 different events containing the letter 'X', but none of the other 5 category letters
• Level 50 requires 50 different events containing the letter 'L', but none of the other 5 category letters
• Level 100 requires 100 different events containing the letter 'C', but none of the other 5 category letters
• Level 1,000 requires 1,000 different events containing the letter 'M', but none of the other 5 category letters

So, I also want a SEQUENCE applied to each one as well: 1, 5, 10, 50, 100 and 1,000 records respectively. I currently have have the following formula, which lets me find the first 20 records beginning with ‘B’:

=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!C4:C2003,LEFT('All Completed Runs'!C4:C2003,1)="B"),0),SEQUENCE(20)),"")

Would there be a way of combining these formula types, to achieve what I’m after?

Thanks in advance!

Olly.

 

[ollyhughes1982]

I have now resolved this, with the formula, below:

=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!C4:C2003,(ISNUMBER(SEARCH("I",'All Completed Runs'!C4:C2003))=FALSE)*(ISNUMBER(SEARCH("V",'All Completed Runs'!C4:C2003))=FALSE)*(ISNUMBER(SEARCH("X",'All Completed Runs'!C4:C2003))=FALSE)*(ISNUMBER(SEARCH("L",'All Completed Runs'!C4:C2003))=FALSE)*(ISNUMBER(SEARCH("C",'All Completed Runs'!C4:C2003))=FALSE)*((ISNUMBER(SEARCH("M",'All Completed Runs'!C4:C2003)))),""),0),SEQUENCE(1000)),"")

Thanks.