Forum Discussion

Rodney2485's avatar
Rodney2485
Brass Contributor
Jun 11, 2024

Trouble removing CALC Error

This is the Formula i'm using, however when the formula doesnt return a result I get the #Calc error.

 

=LET( filtered, FILTER( F2:F9000, ISNUMBER(XMATCH(G2:G9000, H2:H3, )) ), UNIQUE(filtered))

 

I've tried inserting "No Event" into the formula but either I'm not putting it into the string correctly or I need to do something else.

Excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jun 11, 2024
    None of the entries in column G match the condition.
    This formula gives the same result:
    =LET( filtered, FILTER( F2:F9000,(G2:G9000=H2)+(G2:G9000=H3)>0), UNIQUE(filtered))

    And if you convert your data to a table there is no need to refer to this many rows.
  • djclements's avatar
    djclements
    Bronze Contributor
    Jun 11, 2024

    Rodney2485 Here's a few options for handling the #CALC error in this scenario:

     

    =LET(
    filtered, FILTER(F2:F9000, ISNUMBER(XMATCH(G2:G9000, H2:H3))),
    IF(ISNUMBER(ROWS(filtered)), UNIQUE(filtered), "No Records")
)

=LET(
    incl, ISNUMBER(XMATCH(G2:G9000, H2:H3)),
    IF(OR(incl), UNIQUE(FILTER(F2:F9000, incl)), "No Records")
)

=IFERROR(UNIQUE(FILTER(F2:F9000, ISNUMBER(XMATCH(G2:G9000, H2:H3)))), "No Records")

     

    See attached, if needed...

Resources