This is easiest done in a database using Joins, but you can do it in excel using VLOOKUP.
First create a third sheet, and paste the 'dog, cat, etc' columns from sheet one and two into a single column in sheet 3. The use Excel 2007's Remove Duplicates feature to remove duplicates.
Then in the second column, specify the formula =VLOOKUP(A1, Sheet1!A$1:B$100, 2, false) where A1 is the cell to the left of the cell youu're editing (on sheet 3) and A1:B100 is the region on sheet 1 containing data.
In the third column you specify a similar formula, but pointing to Sheet 2, =VLOOKUP(A1, Sheet2!A$1:B$100, 2, false).
You may need to sort the data in sheet 1 and 2, and the names on sheet 3.