arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

Find and repeat a cell value if values match

My spreadsheet is collecting data from a form

Responses from Google Form

On another tab I'm showing the responses that match "Lesson day" with "Today's date" (in the example, all the lessons are showed).

Second tab

As you can see, the "e-mail" field is filled out only once. Is there a way to automatically fill the "e-mail" field in the second tab? Maybe matching the "Student Code" with the "e-mail" fields in the first tab?

I've tried the MATCH() function but it returns only the position. I feel like I need to combine MATCH() with other functions but I have no idea about where to start

EDIT: Having the email adresses on another column would work too. SOmething like: if the "Student Code" in the 2 tabs matches, show the email adress for that "Student code"

Solution

  • try:

    =ARRAYFORMULA(IFNA(VLOOKUP(D4:D, FILTER({form!E2:E, form!G2:G}, form!G2:G<>""), 2, 0)))

    change form to match the sheet name of your form sheet