How would you combine 2 sheets based on 1 field, but combine certain columns not append?

[dsucci]

So i'm trying to combine 2 excel sheets and i'm looking for something similar to index/matching in excel, where say i have 2 tables:

Table 1:

ID	Name	Code	Country
1	Jane Green		Canada
2	John Blue

Table2:

ID	Name	Code	Fruit	Country
1	Jane Green	5NJ	Apple	Canada
2	John Blue	3FC	Pear	USA

So How would i get them to match based on ID but then insert the "code" into table 1 and add in the fruit column while also adding in missing data from Country? In this example, I know they are the same size so i could just keep Table 2, but in my predicament table 1 is significantly bigger and the "master" data, so i'm trying to add in this new information of table2 into table 1. When i use a Join tool it will match based on ID but then just append the "code" onto the end by calling it "Right_code"

[Raj · Accepted solution]

@dsucci 
please find the workflow attached

if this is not the required output
please add the expected output.

mark done if solved.

[ChrisTX]

Let the Join tool add the extra column "Right_Code"

Then use a Formula tool with this expression, to update the field [Code] you want to keep:

IF IsEmpty([Code]) THEN [Right_Code] ELSE [Code] ENDIF

Guessing it's not possible to have a value for Code from both Sheets?

Chris