IF/HAS Formula not returning expected value...

SR2

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

Find more posts tagged with

Accepted answers

All comments

Courtney S.

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

Emory

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:

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.
If the count is greater than 1, it means the value is a duplicate, and the formula returns "Duplicate".
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.

SR2

@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"

Courtney S.

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

Emory

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.