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