compare two lists and create a 3rd list of missing values

[palaeontology]

In the range D6:D300 I have a list of 5-digit student id numbers ... some cells in the range might be empty

In the range E6:E300 I have a list of student id numbers that have submitted a survey response ... some cells in the range might be empty

In the range F6:F300 I would like to return a list of those student id numbers that appear in D6:D300, but not in E6:E300 ... there won't be many id numbers returned in this list, so most of the cells should come back as looking empty

I have seen a few videos showing how to do this using the 'FILTER' function, but I don't seem to have that function (Excel 2016)

Is there a different way to achieve this ... I need the solution to be a formula .. I can't have it as a manual series of steps.

Kind regards,

Chris

 

[earthworm]

Try below .

Book1
	D	E	F	G
6	List A	List B	Helper Coumn	Missing Data
7	Test 1	Test 1	FALSE	Test 5
8	Test 2	Test 2	FALSE	Test 8
9	Test 3	Test 3	FALSE	Test 13
10	Test 4	Test 4	FALSE
11	Test 5		TRUE
12	Test 6	Test 6	FALSE
13	Test 7	Test 7	FALSE
14	Test 8		TRUE
15	Test 9	Test 9	FALSE
16	Test 10	Test 10	FALSE
17	Test 11	Test 11	FALSE
18	Test 12	Test 12	FALSE
19	Test 13		TRUE
20	Test 14	Test 14	FALSE
21	Test 15	Test 15	FALSE
22	Test 16	Test 16	FALSE
23	Test 17	Test 17	FALSE
Sheet1

Cell Formulas
Range	Formula
F7:F23	F7	=ISERROR(VLOOKUP(D7,$E$7:$E$306,1,0))
G7:G23	G7	=IFERROR(INDEX($D$7:$D$306,AGGREGATE(15,6,ROW($D$7:$D$306)-ROW($D$7)+1/($F$7:$F$306=TRUE),ROWS(G$7:G7))),"")

 

[Fluff]

Another option

+Fluff 1.xlsm
	D	E	F
5	List A	List B
6	Test 1	Test 1	Test 5
7	Test 2	Test 2	Test 8
8	Test 3	Test 3	Test 13
9	Test 4	Test 4
10	Test 5
11	Test 6	Test 6
12	Test 7	Test 7
13	Test 8
14	Test 9	Test 9
15	Test 10	Test 10
16	Test 11	Test 11
17	Test 12	Test 12
18	Test 13
19	Test 14	Test 14
20	Test 15	Test 15
21	Test 16	Test 16
22	Test 17	Test 17
23
Lists

Cell Formulas
Range	Formula
F6:F22	F6	=IFERROR(INDEX($D$6:$D$300,AGGREGATE(15,6,(ROW($D$6:$D$300)-ROW($D$6)+1)/(ISNA(MATCH($E$6:$E$300,$D$6:$D$300,0)))/(D6:D300<>""),ROWS(F$6:F6))),"")