Find and repeat a cell value if values match

Question

My spreadsheet is collecting data from a 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).

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"

Answer 1

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

Answer 2

Usually MATCH is combined with INDEX

You should build an auxiliary table with one column for names and another for ee-mails.

---

To build the auxiliary table from the collected data you could use something like this

=UNIQUE(FILTER({Lessons!E:E,Lessons!G:G},LEN(Lessons!G:G)))

The above is better than using a formula for each column as this prevent having mismatching data.

INDEX / MATCH usually doesn't return the "expected" results in an ARRAYFORMULA. Instead use VLOOKUP.

NOTES:

Please bear in mind that every time that you edit a value the formula will be recalculated. If you have a lot of formulas (like when doing a fill down to copy a formula to all the cells in a column) or having an ARRAYFORMULA with open ended references i.e. G2:G this could affect your spreadsheet performance.

If you go for using an ARRAYFORMULA, use ARRAY_CONSTRAIN to limit the number of rows returned by your formula.

Related

• How to map values between columns in worksheets

Answer 3

As suggested, I created an auxiliary table (just called Sheet7) and getting closer to the desired result

In A column I used

=UNIQUE(Lessons!E:E)

In column B I used

=INDEX(Lessons!A:M, MATCH(A2,Lessons!E:E,0),7)

Then, on my second spreadsheet, I could get all the e-mail adress using the following formula

=INDEX(Sheet7!A:B, MATCH(D4,Sheet7!A:A,0),2)

I've tried to use ArrayFormula on the auxiliary table

ARRAYFORMULA(if(A2:A="",,INDEX(Lessons!A:M, MATCH(A2:A,Lessons!E:E,0),7)))

But it doesn't work. It shows only the email adress matched from the cell A2, ignoring A3, A4,...

Answer 4

A different approach:

1. Make an extra column in your form responses sheet called "Student E-mail".(In this example it is column "I")
2. In row 2 of that column, write the following formula:

=ARRAYFORMULA(IF(ISBLANK(G2:G),VLOOKUP(F2:F,INDIRECT("F$2:G"&ROW(I2:I),TRUE),2),G2:G))

3. Use that column for the emails in your other tab (hide the original email column)

---

Update:

Using array formula, you don't have to manually drag & drop. And it keeps going as new rows are added.