Google-sheets – Applying formula to lookup value in array of cells in Google Form Responses

formulasgoogle sheetsgoogle-forms

I have a Google Form response sheet, part of which contains several columns (say B:G) that will only have a single value between them. The form asks respondents for their county of residence and then their town of residence within that county on separate pages as a means to get around the fact that Forms doesn't have support for dependent questions.

Regardless, I'm trying to figure out a formula to return the value contained in these columns for each row, as well as a way to autopopulate this when new form responses are submitted. I gather this might include the use of ArrayFormula, but I haven't been able to figure it out.

Any advice?

Here's a sample sheet with dummy form data and the ideal return.

https://docs.google.com/spreadsheets/d/1EHcjuoNd1wQLbD7ceb1i6lsxWs0iU4w-Ermz095Qmbg/edit

Best Answer

Try:

=ARRAYFORMULA(transpose(split(concatenate(if(B2:D<>"", B2:D,)&char(9)),char(9))))

or:

=ARRAYFORMULA(transpose(split(concatenate(B2:D&char(9)),char(9))))

or also:

=ArrayFormula(transpose(trim(query(transpose(B2:D),,rows(A:A)))))
Related Topic