bringing missing names from the other table

[INN]

Hi
I have 2 tables. The second table is missing some names from the first table. What I want to do, is to find these names that are in table1 but not table2 and bring them to table2 and also bring the other missing columns like Dept, Age, Salary. How can I do that? Thank you very much. Please see tables below

Book1
	A	B	C	D
1	Name	Dept	Age	Salary
2	Mary1	HR	1	10
3	Mary2	IT	2	20
4	Mary3	Sales	3	30
5	Mary4	Marketing	4	40
6	Mary5	HR	5	50
7	Mary6	IT	6	60
8	Mary7	Sales	7	70
9	Mary8	Marketing	8	80
10	Mary9	HR	9	90
11
12
13
14	Name	Dept	Age	Salary
15	Mary1	HR	1	10
16	Mary3	Sales	3	30
17	Mary10	HR	9	90
Sheet1

 

[Sanjeev1976]

You can try the below :

Book1
	A	B	C	D
1	Name	Dept	Age	Salary
2	Mary1	HR	1	10
3	Mary2	IT	2	20
4	Mary3	Sales	3	30
5	Mary4	Marketing	4	40
6	Mary5	HR	5	50
7	Mary6	IT	6	60
8	Mary7	Sales	7	70
9	Mary8	Marketing	8	80
10	Mary9	HR	9	90
11
12
13
14	Name	Dept	Age	Salary
15	Mary1	HR	1	10
16	Mary3	Sales	3	30
17	Mary10	HR	9	90
18	Mary2	IT	2	20
19	Mary4	Marketing	4	40
20	Mary5	HR	5	50
21	Mary6	IT	6	60
22	Mary7	Sales	7	70
23	Mary8	Marketing	8	80
24	Mary9	HR	9	90
Sheet1

Cell Formulas
Range	Formula
A18:D24	A18	=FILTER(A2:D10,COUNTIF(A15:A17,A2:A10)<>1)
Dynamic array formulas.

 

[INN]

This works so nice. Thank you so much. Can I ask you this question please:
Why when I do this

=FILTER(A2:D10,COUNTIF(A2:A10,A15:A17)<>1)

It does not work?

Thanks once again.

 

[Sanjeev1976]

In the countif formula used by you, you are counting whether cells A15:A17 exist in range A2:A10, out of which since 2 cells already exist, so nothing will return and the 3rd does not exist, so still nothing will return and hence you get "#Value!" error. In the countif formula used by me, as required, I get all the cells which does not exist in range A15:A17.

 

[Peter_SSs]

Another way to look at it would be ..

23 03 11.xlsm
	A	B	C	D
1	Name	Dept	Age	Salary
2	Mary1	HR	1	10
3	Mary2	IT	2	20
4	Mary3	Sales	3	30
5	Mary4	Marketing	4	40
6	Mary5	HR	5	50
7	Mary6	IT	6	60
8	Mary7	Sales	7	70
9	Mary8	Marketing	8	80
10	Mary9	HR	9	90
11
12
13
14	Name	Dept	Age	Salary
15	Mary1	HR	1	10
16	Mary3	Sales	3	30
17	Mary10	HR	9	90
18	Mary2	IT	2	20
19	Mary4	Marketing	4	40
20	Mary5	HR	5	50
21	Mary6	IT	6	60
22	Mary7	Sales	7	70
23	Mary8	Marketing	8	80
24	Mary9	HR	9	90
Add missing

Cell Formulas
Range	Formula
A18:D24	A18	=FILTER(A2:D10,ISNA(MATCH(A2:A10,A15:A17,0)))
Dynamic array formulas.

 

[INN]

Thank you all for the help. I appreciate it.