1

Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row.

Currently using =IF(COUNTIF($A2:$EI2,"="&EO$1)>0,1,0) to search the row and if the value is found at least once return 1, else return 0. That way I have a value to reference for a conditional trigger elsewhere. The problem is in order to apply this to a new row I have to drag it down. The sheet is populated by Google Form responses so I will have an indeterminate number of rows and would like the formula to automatically work on future rows without having to drag down.

Is there a way to perform a row search as an ARRAYFORMULA? If it helps, I don't actually need to search the entire row, I only need to search all the columns with a certain header (the header is the same as the question is repeated throughout the form as respondents won't complete every section of the form).

Edit: Here's a link to a dummy sheet with the scenario.

Improve this question
1
  • Edited original post to include a dummy sheet link.
    – hartj
    Feb 15, 2019 at 17:50

3 Answers 3

Reset to default
0 

=ARRAYFORMULA(IF(LEN(EO1:EO), IF(COUNTIF($A1:$EI, "="&EO1:EO)>0, 1, 0), ))

Improve this answer
5
  • Doesn't the COUNTIF in this solution search the entire range A1:EI each time? I want the COUNTIF to only count one row at a time. This issue is, if row 1 has a hit but row 2 doesn't, the provided formula would still return 1 in both row 1 and row 2. I want the ARRAYFORMULA to return 1 in row 1 but 0 in row 2 if the key only exists in row 1 and not row 2.
    – hartj
    Feb 21, 2019 at 20:49
  • well, it does exactly what you ask. it looks in individual rows and searches for a value listed in EO column of the respective row. A1:EI part is just for array formula so arrayformula would know where to stop
    – user0
    Feb 21, 2019 at 21:16
  • I think there's a misunderstanding. My goal is to search an entire row for a value listed as a column header. With each column being on of the currently 26 possible search terms. Thus creating a matrix that identifies which of the 26 search terms exist in the row. Note the sample sheet. fee, fi, fo, etc. exist in the rows as form responses and exist as column headers on the far right side of the sheet. The far right columns track if the given term did (1) or did not (0) exist in the row.
    – hartj
    Feb 25, 2019 at 17:57
  • I see. in that case you can use HLOOKUP for that to generate 1/0 for the rest of the row (answer added) but you will still need to drag down the arrayformula in the first column to be this applied for each row
    – user0
    Feb 26, 2019 at 4:51
  • That's the trouble, can't drag down. Google Forms responses create a new row when adding data and the formula needs to run instantaneously to function with an automated mail merge program. I have a tedious short term fix where I simply call out each and every column by name... but ideally the goal is that the form will change year over year and I don't want to have to keep hand typing every column to check. Using =ARRAYFORMULA(IFERROR(IFS($M$2:$M=EK$1,1,$N$2:$N=EK$1,1,$AR$2:$AR=EK$1,1,...many many more here...,$EI$2:$EI=EK$1,1),))
    – hartj
    Feb 26, 2019 at 17:46
0

You could use

=IF(CONCATENATE($A2:$EI2)<>"",IF(COUNTIF($A2:$EI2,"="&EO$1)>0,1,0),"")

and copy downwards, for as many rows as you foresee you may use. This avoids array formulas.

Improve this answer
1
  • The goal is to make something that automatically copies downward. If I wanted to copy downward I would just use the function mentioned in my question. (Though thank you for the link to read!)
    – hartj
    Feb 21, 2019 at 20:30
0 

=ARRAYFORMULA(IF(IFERROR(HLOOKUP($M$1:$P$1, A2:L2, 1, 0), )="", 0, 1))

Improve this answer
2
  • @hartj well how about creating 2nd spreadsheet and import your form spreadsheet there with IMPORTRANGE and then have this formula pre-populated all over the whole column and then use again IMPORTRANGE formula to import data back into form spreadsheet in an array - this way when a new row will be added it will be cloned into 2nd spreadsheet where 0/1s will be calculated and sent back in form spreadsheet. it wont be instant byt 1-2sec delay is I guess ok
    – user0
    Feb 26, 2019 at 17:58
  • Tedious, but less tedious than hand typing the column call outs... I'll give it a try and see if it works with the merge add-on. Thanks for sticking with this!
    – hartj
    Feb 26, 2019 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.