How to insert row and keep all the data together

Hello,

I am having trouble figuring this problem out and hoping someone can help. I have attached an example of what we are dealing with. I have a online spread sheet that several people work with me on represented in Sheet1. Then we have another sheet which is represented by Sheet2 that has same data but also has additional data added to it.

Some constraints we have are the following:

• We need to keep the sheets separate.
• New rows may get added from time to time on Sheet1 but not Sheet2.
• There are columns on Sheet1 that are not on Sheet2 but Sheet 2 should contain all the columns from both.

The problem we are having is keeping the data together when we insert a row. I tried using Indirect() and that works well except it does not work with the columns in Sheet2 that are not in Sheet1.

in the attached example. The attached example, I inserted a row in-between the names Sam and Michelle and it worked fine for everything except for the Color data on Sheet2. Michelle should be brown but the data didn't shift downward.

Does anyone know a good way to accomplish this task?

Thank you Glenn. I have been tinkering with it and decided to use VLOOKUP as you suggested to get where I needed to go. Thank you for your advice.