Reference column in second table using filtered column

Hi all, 

I am trying to replicate the above. 

I have 2 tables which can be described as - 

Table 1: Product name and share columns, there is also retailer & state columns (not shown above)
Table 2: Product name and competitive set ranked (not shown above)

Visualisation: 

in a matrix, with drop-down filters on retailer, state & Name show (eg. Walmart, CA, Prod 1)

Name product, Name product share, comp 1, comp 1 share. 

I have tried a number of formulas and so far most of my results for comp return (blank)