Why is my Excel formula returning 0 instead of "Not found" when no values should have been found?

Question

I'm trying to use a formula on Excel in order to:

(a) Compare all values from two columns, D and R;

(b) Identify which values are contained in R that are not contained in D;

(c) Provided that for each value in a cell of D; there is a value in the same row of column A equal to cell DRM!$W$1

(d) Ignore values in rows 1:5 of all these columns; (e) List all those values contained in R that are not contained in D, starting from cell AR6

Here's the formula I'm using:

=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))))

The formula works fine when there's a match in column D, but when there's no match, it returns 0 instead of the expected text "Not found". Can someone explain why this is happening?

There ARE different values on both columns D and R, but they aren't classified as "IRRBB", so the answer should have been "Not found".

I tried both using this formula as matrix (Ctrl + Shift + Enter) and as the standard formula. Funny enough, when I used the same formula as matrix for columns Z and K instead of R, I got the right answer.

How can I get rid of the answer 0 and get the answer Not found?

I'm using Microsoft® Excel® for Microsoft 365 MSO (Versão 2212 Build 16.0.15928.20278) 64 bits on Windows 10.

These are all the values in the columns and cells:

DRM!$W$1 = IRDC

Colum A (...)   Colum D (...)   Colum R
DATREF              
Rel             
                
Lize        Chave BC_P      Chave BC_P
IRDC        JP1_CDI     JP1_CDI
IRDC        JP1_COM     JP1_COM
IRDC        JP1_CRA     JP1_CRA
IRDC        JP1_DEB     JP1_DEB
IRDC        JP1_PRO     JP1_PRO
IRDC        JP1_REN     JP1_REN
IRDC        JP1_REP     JP1_REP
IRDC        JM1_REP     JM1_REP
IRDC        JM1_SWA     JM1_SWA
IRDC        JM2_REC     JM2_REC
IRDC        JM2_SWA     JM2_SWA
IRDC        JI2_OPE     JI2_OPE
IRDC        JI2_PES     JI2_PES
IRDC        JI1_COM     JI1_COM
IRDC        JI1_NTN     JI1_NTN
IRDC        JI1_OPE     JI1_OPE
IRDC        JI1_REC     JI1_REC
IRDC        JI1_REN     JI1_REN
IRDC        JI1_REP     JI1_REP
IRDC        JI1_REP     JI1_REP
IRDC        JJ1_CAP     JJ1_COM
IRDC        JJ1_CAP     JJ1_OPE
IRDC        JJ1_CDB     JJ1_PES
IRDC        JJ1_COM     JJ1_PRO
IRDC        JJ1_OPE     JJ1_REC
IRDC        JJ1_PES     JJ1_REN
IRDC        JJ1_PRO     JJ1_REP
IRDC        JJ1_REC     JJ1_REP
IRDC        JJ1_REN     JJ1_REP
IRDC        JJ1_REP     JJ1_REP
IRDC        JJ1_REP     JJ1_REP
IRDC        JJ1_REP     JJ1_REP
IRDC        JJ1_REP     JP2_COM
IRDC        JJ1_REP     JP2_LFT
IRDC        JJ1_REP     JP2_PRO
IRDC        JJ1_SWA     JP2_REC
IRDC        JP2_COM     JP2_REN
IRDC        JP2_LFT     JP2_REP
IRDC        JP2_PRO     JP2_REP
IRDC        JP2_REC     JP2_REP
IRDC        JP2_REN     JT2_REC
IRDC        JP2_REP     JT2_REN
IRDC        JP2_REP     JT2_REP
IRDC        JP2_REP     JT2_REP
IRDC        JT2_REC     JI3_REN
IRDC        JT2_REN     JI3_REP
IRDC        JT2_REP     JT1_COM
IRDC        JT2_REP     JT1_OPE
IRDC        JI3_REN     JT1_REC
IRDC        JI3_REP     JT1_REN
IRDC        JT1_COM     JT1_REP
IRDC        JT1_OPE     JT1_REP
IRDC        JT1_REC     JT1_REP
IRDC        JT1_REN     JP1_CAP
IRDC        JT1_REP     JP1_CAP
IRDC        JT1_REP     JP1_CDB
IRDC        JT1_REP     JP1_SWA
IRDC        JP1_CAP     JM1_CAP
IRDC        JP1_CAP     JM1_REP
IRDC        JP1_CDB     JM2_CAP
IRDC        JP1_SWA     JI2_PES
IRDC        JM1_CAP     JI1_CDB
IRDC        JM1_REP     JI1_REP
IRDC        JM2_CAP     JI1_REP
IRDC        JI2_PES     JJ1_CAP
IRDC        JI1_CDB     JJ1_CAP
IRDC        JI1_REP     JJ1_CDB
IRDC        JI1_REP     JJ1_REP
IRDC        JJ1_CAP     JJ1_REP
IRDC        JJ1_CAP     JJ1_REP
IRDC        JJ1_CDB     JJ1_REP
IRDC        JJ1_DEB     JJ1_REP
IRDC        JJ1_REP     JJ1_SWA
IRDC        JJ1_REP     JP2_REP
IRDC        JJ1_REP     JP2_REP
IRDC        JJ1_REP     JT2_REP
IRDC        JJ1_REP     JT2_REP
IRDC        JJ1_SWA     JI3_REP
IRDC        JP2_REP     JT1_REP
IRDC        JP2_REP     JT1_REP
IRDC        JT2_REP     
IRDC        JT2_REP     
IRDC        JI3_REP     
IRDC        JT1_REP     
IRDC        JT1_REP     
IRDC Total              
NA      998_DIS     
NA      999_COT     
NA      999_COT     
NA      999_COT     
NA      ME1_DEP     
NA      ME1_REP     
NA      ME1_SWA     
NA      ME2_DEP     
NA      ME2_REC     
NA      ME2_SWA     
NA      AA1_ACO     
NA      JJ1_COM     
NA      ME1_CAP     
NA      ME1_REP     
NA      ME2_CAP     
NA Total                
Grand total

Thanks in advance for any help you can provide!

I strongly suspect that the formula for this problem can be significantly shortened and simplified. Could you just give an example of the expected outcome? More explicitly, I'm wondering if you want the outcome to show criteria matching rows in their respective row, or if you just need an unstructured list of all matching rows. — Dattel Klauber, Mar 07 '23 at 23:14
[Evaluate step by step](https://support.microsoft.com/en-us/office/evaluate-a-nested-formula-one-step-at-a-time-59a201ae-d1dc-4b15-8586-a70aa409b8a7) your formula where you know it should return `Not Found` and see the exact moment it does not work as intended. — Foxfire And Burns And Burns, Mar 09 '23 at 12:54

score 2 · Accepted Answer · answered Mar 10 '23 at 06:57

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 ) )

Why is my Excel formula returning 0 instead of "Not found" when no values should have been found?

1 Answers1