Assitance with Lookup function for matching two sheets of data

Question

Basically I have two sheets (s1 and s2), and the data is formatted something like below, first is S1:

ID     Name
1      Prod 1
2      Prod 2
3      Prod 3
4      Prod 4
5      Prod 5
6      Prod 6

Next is S2:

ID     Name
1      Prod 1
2      Prod 2
4      Prod 4
6      Prod 6

You will notice that the data doesn't exactly match up from both sheets, what I want is in a third sheet which I'll be calling S3, to combine the data so it looks something like this:

    S1:              S2:
ID     Name       ID     Name
1      Prod 1     1      Prod 1
2      Prod 2     2      Prod 2
3      Prod 3     N/A    N/A
4      Prod 4     4      Prod 4
5      Prod 5     N/A    N/A
6      Prod 6     6      Prod 6

I hope that is clear, in S3 I've currently used:

=S1!A2 and =S1!B2

To import the data (ID and Name) from S1 into S3, then:

=LOOKUP(A2,S2!$A$2:$A$30)

In an attempt to match things up from S1 to S2 in S3 like in my example, but I've yet to have any success with it, This (not working) code above is obviously just for the ID, I'm unsure how I would even attempt matching up the data in "Name" how I need it as well as the ID.

Any help or nudges in the right direction would be greatly appreciated, let me know if clarifications or more info is needed, my apologies in advance, my Excel skills are exceedingly poor.

Answer 1

You don't need LOOKUP for the IDs, you can use COUNTIF (or MATCH):
=IF(COUNTIF(Sheet2!A:A,A2)>0,A2,"N/A")

And use lookup only for the name:
=IF(C2="N/A","N/A",VLOOKUP(C2,Sheet2!A:B,2,FALSE))