Display Value after Matching from Two Separate Tables

What would be the formulat to to display the unit “Three” in Column H if given the unit “Two” and “Four”?

It would have to match and validate in two seperate tables; Table 1 (Column A:B) and Table 2 (Column C:E). 

For example, if given unit Two=10, it should first validate to match Table 1 where it gives the possible unit(s) of “Three”. Then it should validate with Table 2 for a valid match to display the unit “Four” from Table 2 in Column H. 

 

Seems a little confusing with two tables, any method or formula would be appreciated. Maybe I can try consolidating the two tables into one and/or use some like of Index or Vlookup with Sumproduct? I cannot seem to formulate any of this. 

 

Table 1 Table 2 Given

Two Three   Two Three Four   Two Four Three
10  110   10  110 9000   10 9000 110
10 111   10 122 9000   Given Given Displays
11 110   11 110 9101        
12 111   12 130 9202        
                     
                     
                     
                     
                     
                   

By: Brian

Leave a Reply

Your email address will not be published. Required fields are marked *