Is Store # present in list?

I have a survey that is to be completed by a set # of store locations (896 locations).
I have a formula that will check the worksheet containing the survey inputs for store #. If Store # is absent then checkbox is checked.
I want to built a lookup such that if you enter a Store # the result will either be;
'Survey has been completed'
or
'Survey has not yet been completed'

my lookup formula is;
=IF(CONTAINS([Store #]@row , {3rd Wave outstanding surveys-Store List}), "Survey NOT yet submitted", "Survey has been received")

Below is screenshot of Lookup. Store # is 7.

Below is screenshot of worksheet which is identifying which stores have surveys submitted or not.

Survey for Store #7 has been received evidenced by the check.

Store #7 is NOT part of the 'List of stores yet to complete survey', yet my lookup says that survey has NOT yet been submitted.

I have also tried this formula;

=IF(HAS({3rd Wave outstanding surveys-Store List}, [Store #]@row ), "Survey NOT yet submitted", "Survey has been received")

Below screenshot is result. Store #3 IS present in 'List of stores yet to complete survey', however, lookup is indicating that it has been received.

I'm stumped.

Find more posts tagged with

Formulas

Accepted answers

heyjay

Change your Value column to dropdown, allow multiple selection.
Use a formula something like,

=IF(HAS(Value:Value, 7), 1, 0)

Replace:
VALUE - with your column

7 - with your reference cell

1 - TRUE Value

0 - FALSE Value

Use HAS and DROPDOWN - incase you have 7 17 27 37, it will check for the numbers individually, it should not trigger TRUE when value 17 27 37 is present when looking for the number 7.

All comments

Jgorsich

Seems easier to just check your "Survey Completed" column with something like this:

=if(sumif([Store #]:[Store #],Lookup@row,[Trip Hazard Survey - 3rd Wave Complete?]:[Trip Hazard Survey - 3rd Wave Complete?])>0,"Survey has been received","Survey NOT yet submitted")

heyjay

Change your Value column to dropdown, allow multiple selection.
Use a formula something like,

=IF(HAS(Value:Value, 7), 1, 0)

Replace:
VALUE - with your column

7 - with your reference cell

1 - TRUE Value

0 - FALSE Value

Use HAS and DROPDOWN - incase you have 7 17 27 37, it will check for the numbers individually, it should not trigger TRUE when value 17 27 37 is present when looking for the number 7.

PeggyLang

@heyjay
THANK YOU! Changing column properties to 'Dropdown, multiple select' was the key.
I have used this before in a somewhat complex 'Technician On Call' solution, but TOTALLY forgot that the column containing all the stores where surveys were not yet submitted needed to be Dropdown, multiple select.

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.