Nested if statements creating priorities

[uceaamh]

Hello,

I'm struggling with a formula which has way too many nested IF functions.

The goal is to create a priority list for the IFs. To explain further -

I'm trying to extract a unique identifying number (henceforth #) given a set of conditions. The conditions are:

1. If # is attached to Note Type 1, and that was created after DATE, check if # has been extracted before. If so, start again for next #. If not, extract and stop
2. If # is attached to Note Type 1, and that was created before DATE, then start again for the next #
3. If # has no Note Type 1 attached, check next criteria
4. If # is attached to Note Type 2, and that was created after DATE, check if # has been extracted before. If so, start again for next #. If not, extract and stop
5. If # is attached to Note Type 2, and that was created before DATE, then start again for the next #
6. If # has no Note Type 2 attached, check next criteria
7. If # is attached to Note Type 3, and that was created after DATE, check if # has been extracted before. If so, start again for next #. If not, extract and stop
8. If # is attached to Note Type 3, and that was created before DATE, then start again for the next #
9. If # has no Note Type 3 attached, start again for next #

So it's a lot of complex criteria. My formula is:

=INDEX(Data[Premises - Key],

MATCH(0,

IF(REFERENCES!$C$12>=Data[Creation Date]&(Data[Notice Type]=REFERENCES!$C$3),

COUNTIF($A$1:$A1,Data[Premises - Key]),

IF(REFERENCES!$C$12<=Data[Creation Date]&(Data[Notice Type]=REFERENCES!$C$3),"",

IF(REFERENCES!$C$12>=Data[Creation Date]&(Data[Notice Type]=REFERENCES!$C$5),

COUNTIF($A$1:$A1,Data[Premises - Key]),

IF(REFERENCES!$C$12<=Data[Creation Date]&(Data[Notice Type]=REFERENCES!$C$5),"",

IF(REFERENCES!$C$12>=Data[Creation Date]&(Data[Notice Type]=REFERENCES!$C$7),

COUNTIF($A$1:$A1,Data[Premises - Key]),

IF((REFERENCES!$C$12<=Data[Creation Date]&(Data[Notice Type]=REFERENCES!$C$7),""))))))),0))

Where The unique # I want is Data[Premises - Key], the DATE is REFERENCES!$C$12, Note Type 1 is REFERENCES!$C$3, Note Type 2 is REFERENCES!$C$5, and Note Type 3 is REFERENCES!$C$7.

I know it's a big ask. Thanks for giving my post a look!