Excel formatting not working for some cells when trying to do center alignment

The problem is that you are basiing your reference to the parent purely on position by using a simple formula. What you need to to do is adjust Table 2 so that it uses a pointer to the proper value in Table 1.

        A       B       C           D
1   Id      Price   Parent      Parent Price
2   2001    50.00   1001        =VLOOKUP(C1, Sheet1!$A$1:$B$30, 2, FALSE)
3   2002    2.81    1002        =VLOOKUP(C2, Sheet1!$A$1:$B$30, 2, FALSE)
4   2003    12.00   1003        =VLOOKUP(C3, Sheet1!$A$1:$B$30, 2, FALSE)

The VLOOKUP function takes three parameters. The first is the value you seek. The second is the range that contains the data such that the first column contains the values which will be used for matching. The last argument is the column in that range that should be return. For example, the first formula is going to Sheet1!$A$1:$B$30 (assuming Table 1 is on Sheet1 in cells A1 to B30) and in the first column looking for the value 1001. When it finds it, it then returns the second column in that range. The $ tells the system to use Absolute references so that when you copy your formulas in Table 2, that range will not change.

In this way, no matter how you re-sort Table 1, the formulas will continue to seek a specific value in that range.

It's a known issue. See here: https://support.microsoft.com/en-us/kb/169101

This problem occurs if the cell is not large enough to display all of the text it contains.

Text will not bottom-align unless the cell is high enough to show all text. Kind of defeats the purpose of bottom align and it is not a solution to your question, just an explanation.

Don't shoot the messenger.