Can someone help with a formula that checks a box when 2 criteria are met?

Im looking for a box to get checked when two criteria are met and one of those criteria needs to use the contains function. in the image below im trying to check the box if the "PO #" column is contained in my reference sheet and the "Glass Type" is a match in the reference sheet.

below is what i have but its unparseable:

=IF(CONTAINS([PO #]@row , {Glass Inventory Intake / Form Range 2}) AND([Glass Type]@row ,={Glass Inventory Intake / Form Range 1}),1,0)

Find more posts tagged with

Accepted answers

nicksnav

This formula below seems to work for 5 digit PO numbers with anything listed in front of them. This should reference the last 5 characters in the PO # on the source sheet.

=IF(INDEX(collect({Glass Inventory Intake / Form Range 1}, {Glass Inventory Intake / Form Range 2}, [po #]@row = right(@cell ,5)),1) = [Glass Type]@row , 1, 0)

The problem with using the CONTAINS function in your scenario I'm finding, is that it doesn't work with purely numeric characters. That means CONTAINS would find the PO numbers within something like "PO61851", but wouldn't find the PO numbers in an exact numerical match.

Let me know if this works!

All comments

AngB789

@Brandon Morales Are you looking to have that checkbox when those 2 fields are filled out on that sheet (your screenshot) or are you looking for it to match certain criteria?

Brandon Morales

I need that box to be checked when the first field is present at least once in a particular column on another sheet and when the second fields numbers are contained at least once in a particular column of another sheet.

nicksnav

I believe I understand what you're looking for. The formula below will check the box in the [recieved] column if your PO# is listed in your source sheet with the correlating glass type.

=IF(INDEX({Glass Inventory Intake / Form Range 1}, MATCH([PO #]@row, {Glass Inventory Intake / Form Range 2}, 0)) = [Glass Type]@row, 1, 0)

Let me know if this works!

Brandon Morales

How would it look if I wanted to use CONTAINS instead of MATCH? For the “PO column”, since it is not a drop down some users will simply enter the PO number and some will use the prefix “PO” before the number.

nicksnav

This formula below seems to work for 5 digit PO numbers with anything listed in front of them. This should reference the last 5 characters in the PO # on the source sheet.

=IF(INDEX(collect({Glass Inventory Intake / Form Range 1}, {Glass Inventory Intake / Form Range 2}, [po #]@row = right(@cell ,5)),1) = [Glass Type]@row , 1, 0)

Let me know if this works!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.