Formula (Array, etc.) for automatic Google Sheets Indexing using inputs from Google Forms

Question

I'm hoping that someone can help me tweak (or even substitute) a formula that I'm using in Google Sheets to automatically populate columns with information based on inputs from a Google form.

Simply put, I am using the Index function to match the name that is selected from a drop-down menu in the Google Form and arrives in Column E of the Google Sheet receiving the responses with an identical list of names in Column A of 'Sheet 2'. The index formula takes information from 'Sheet 2' relating to that name (e.g. Registration Number, Email Address) and places it in the 'Formresponses 1'sheet alongside the inputs from the Google form (including, of course, the name that appears in Column E'.

I have been using (variations on) the following formula without any issues, but I have to manually drag it down the relevant column in 'Formresponses 1' each time a new entry/name arrives from the Google Form: =index(Sheet2!$B$2:$B,match(E2, Sheet2!$A$2:$A,0),1)

I have successfully used Array Formulas to automatically carry out other functions on data arriving from a Google Form (i.e. adding up individual numbers to arrive at an overall total), but in this case I cannot figure out how to create a formula that will automatically take each new name that arrives in column E and insert it into the relevant indexing formula at that end of that new row.

Any suggestions - or solutions! - would be greatly welcome!

Thanks,

A.

Thanks for the suggestion. Do you have an example of what that might look like based on the Index formula I'm using at the moment? =index(Sheet2!$B$2:$B,match(E2, Sheet2!$A$2:$A,0),1) — A Warde
Cheers. I've used your suggestion as a starting point and, with a bit of research, I've come up with the following working formula: = ArrayFormula(vlookup(E2:E, Sheet2!A:E, {1,2,3,5},FALSE)) As with my previous problems with array formulas, I found the following website really useful, so full credit has to go to it: benlcollins.com Thanks again for your help. — A Warde

A Warde A Warde · Accepted Answer · 2018-04-13T14:16:05

Cheers I'-'I,

I've used I'-'I's response to my original question here as a starting point and, with a bit of research, I've come up with the following working formula: = ArrayFormula(vlookup(E2:E, Sheet2!A:E, {1,2,3,5},FALSE))

[The curly brackets simply indicate the columns in Sheet 2 from which I want to pull pieces of data relating to each name that is matched up in the 'front end' sheet receiving the responses from the Google Form.] As with my previous problems with array formulas, I found the following website really useful, so full credit has to go to it: benlcollins.com

Formula (Array, etc.) for automatic Google Sheets Indexing using inputs from Google Forms

1 Answers