If((Find)) not functioning properly

I frequently use the formula IF((FIND)) formula to create indicator columns with 1s and 0s or emojis.

In my latest project, however, it has not been working out as I want:

The above is a screenshot of a Grid View of form results.
The field “BACKEND_Expertise Category Prefix” is a lookup field that shows the subject area codes of subject areas that our respondents selected from a Linked Record question.

For example in the fifth row, you see “SB, SB, SB, SS, SH”; that’s because the respondent selected the options for “Early Childhood education;” “Out-of school learning;” and “Parent Teacher Involvement;” which are all coded “SB” (which in our taxonomy system, means they fall under Education).

I then created dummy variables/fields using the formula field type to spit out 1 or 0.

IF( FIND(“SB”, {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}), 1, 0 ) = so that all records that have “SB” in the lookup field will get a “1” in the column “Education”, and then I can sum up how many respondents chose that subject area. Basically, the formula looks for the two-letter code from within the lookup field.

^^I have around 10 formula fields with the formula above with switched two-letter codes; e.g. IF(FIND(“SF”,… / IF(FIND(“SG”,… and so forth

However, the output is unreliable.

In some rows where there is an SB, the column for SB has a 0. I also noticed that for each record, only one of these dummy columns get a 1 in it, which is strange… Finally, I found that the comma does not make a difference. I thought maybe changing F(FIND(“SF” → to F(FIND(“SF,” will fix the problem, but it didn’t.

Hi @Adi_Menayang!
Try this

IF( FIND('SB', {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}&''), 1, 0 ) 

What is most likely happening is that you are looking for ‘SB’ in an array and not a string.
Adding &’’ after the field name fixes this. Someone else can probably explain the exact why’s and how’s better, but this usually helps me.


This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.