Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    IF/HAS Formula not returning expected value...

    ✭
    in Formulas and Functions

    I have the below formula that is searching for duplicate values within one column, compared to a row cell value:

    =IF(HAS(Renter:Renter, Condo@row), "Duplicate", "NOT")

    I tested this formula with duplicate values in both columns and it is returning the False value (NOT)..

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭

      Hi! the values that are in the Renter column's cells, are they exactly the same answer as in the Condo column? Per the HAS function page: "The HAS function is designed to work with multi-select dropdown and multi-contact columns. It won’t cause an error if used with other column types, but it will only return true if the criterion exactly matches the contents of the cell or a distinct value within a multi-select or multi-contact cell."

      HAS Function | Smartsheet Learning Center

      If you want the value that is in the Condo cell to be found if it is contained within any of the Renter column's answers, then you will want to look use the CONTAINS function instead, eg. IF(CONTAINS(Condo@row, Renter:Renter), "Duplicate", "NOT")

      CONTAINS Function | Smartsheet Learning Center

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi SR2,

      The formula you provided seems to be using the HAS function to check if a value in the "Renter" column matches the value in the "Condo" column of the current row. However, the HAS function is generally used to check if a cell contains a specific value within a range, not to directly compare across columns.

      To achieve what you want, we need to use a different approach. You can use the COUNTIF function to check if the value in the "Condo" column of the current row appears more than once in the "Renter" column. Here’s an updated formula:

      =IF(COUNTIF(Renter:Renter, Condo@row) > 1, "Duplicate", "NOT")

      This formula works as follows:

      1. COUNTIF(Renter:Renter, Condo@row) counts the number of times the value in the "Condo" column of the current row appears in the "Renter" column.
      2. If the count is greater than 1, it means the value is a duplicate, and the formula returns "Duplicate".
      3. Otherwise, it returns "NOT".

      You may need to use this formula (If the condo column is left blank):
      =IF(ISBLANK(Condo@row), "NOT", IF(COUNTIF(Renter:Renter, Condo@row) > 1, "Duplicate", "NOT"))

      Please try this formula and let me know if it works for you.

      · Share on FacebookShare on Twitter
    • ✭

      @Emory When trying "=IF(COUNTIF(Renter:Renter, Condo@row) > 1, "Duplicate", "NOT")" , I receive a #INVALID VALUE output…

      @Courtney S. I receive the same #INVALID VALUE output when trying to use "CONTAINS" instead of "HAS"

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      Could you provide examples of the types of answers in the two columns, and also the Column Type of the two?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      If possible, please provide a screenshot of the columns you are using and a few of the rows (for examples).I believe your error in the formula may be due to a column type issue or an incorrect reference. Please make sure you update the formula to match your column name.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions