I think you may have a parenthesis in the wrong place in your formula. I pasted your formula into Notepad++ counted 22x ( but 24x ) . That's no good. That almost always means you are closing off some nested part of the formula sooner than you think you are.
I believe the following two highlighted parenthesis are placed in the wrong place:
=IF(ISERROR(INDEX($R$6:$R$1000,SMALL(IF(ISERROR(MATCH($R$6:$R$1000,$D$6:$D$1000,0))),IF($A$6:$A$1000=DRM!$W$1,ROW($R$6:$R$1000)-ROW($R$6)+1)),ROW()-ROW($AS$5))) ) ,"Not found",INDEX($R$6:$R$1000,SMALL(IF(ISERROR(MATCH($R$6:$R$1000,$D$6:$D$1000,0))),IF($A$6:$A$1000=DRM!$W$1,ROW($R$6:$R$1000)-ROW($R$6)+1)),ROW()-ROW($AS$5))) )
That first superfluous ) closes off the completion of the IF() statement so you never make it to the "what if FALSE?" result of the IF.

Here's how I solve/prevent that...

I use the LET() function to help me simplify really complex formulas, and once you get used to it, it’s life-changing. Using LET(), combined with using ALT-Returns to create non-breaking carriage returns in the formula is a huge simplifier. LET allows you to separate the part of the function that gathers all the data, from the part that actually performs the logic.
LET() is only available in O365/Excel 2021, but you have that, so we're good there.
Your formula, as best I can tell, becomes:

LET(
    rA5, ROW($A$5),
    rA6, ROW($A$6),
    rR6, ROW($R$6),
    rngR, $R$6:$R$1000,
    rngD, $D$6:$D$1000,
    rngA, $A$6:$A$1000,
    rngRplus, ROW(rngR)-ROW(rR6)+1,
    rA5minus, ROW()-ROW(rA5),
    matchR_D, IF(ISERROR( MATCH( rngR,rngD ,0))),
    equalsA_W, rngA=DRM!$W$1,
    ifAW_rngR, IF(equalsA_W,rngRplus ),
    fullMonty, INDEX(rngR,SMALL(matchR_D,ifAW_rngR),rA5minus),
    IF( ISERROR( fullMonty ) ) , "Not found" , fullMonty )
)

I may not have the descriptions worded quite right, but you can see how it just changes the complexion of the whole formula and simplifies debugging. It also cuts execution time in half because the big hairy INDEX function is only performed one instead of twice.
Using this annotation I can immediately and easily spot my premature ) .
Absent all the defintions, what you want the logic format of your formula to be is:
=LET( IF( ISERROR( fullMonty ) , "Not found" , fullMonty ) )