I have a list of names, which serves as a look-up table, with a fill-in color applied to the cell (i. e., "Doe, Jane A" and her fill-in cell color is some shade of green). Other names have different colors. The lookup table has names in Column A and team names in column B.

I have other worksheets that have the name of "Doe, Jane A" in a cell. I want to color the row of data where this name appears with the fill-in green color copied from the list of names. I'd prefer to not use Conditional Formatting to accomplish this since the names and colors in the look-up table will change and I'd rather not have to apply new VBA code whenever the list of names is amended (hence my desire to have the colors copied from the look-up table).

I was trying to accomplish changing the fill-in color in one cell using an If statement as below, but I get #NAME?. In the example shown below, cell B2 is the cell where the name appears on the worksheet and 'Team Lookup Chart' is the aforementioned lookup table. I was hoping to use the Index function because of the changeable nature of the data entered into the lookup table.

Thanks in advance. Any help would be greatly appreciated! 

=IF(ISNUMBER(SEARCH("Doe, Jane A",B2)), (INDEX('Team Lookup Chart'!$A:$B, (A2.Interior.Color.paste), 1)))