What would be the formula to display the unit "Three" in Column H if given the unit "Two" and "Four"?
It would have to match and validate in two separate 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 (using 10 and possible unit Three) and finally the corresponding unit "Four" from Table 2 should display in Column J.
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
<tbody>
</tbody>
<tbody>
</tbody>
It would have to match and validate in two separate 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 (using 10 and possible unit Three) and finally the corresponding unit "Four" from Table 2 should display in Column J.
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
| | A | B | C | D | E | F | G | H | I | J |
| 1 | Two | Three | Two | Three | Four | Two | Four | Three | ||
| 2 | 10 | 110 | 10 | 110 | 9000 | 10 | 9000 | 110 | ||
| 3 | 10 | 111 | 10 | 122 | 9000 | Given | Given | Displays | ||
| 4 | 11 | 110 | 11 | 110 | 9101 | |||||
| 5 | 12 | 111 | 12 | 130 | 9202 |
<tbody>
</tbody>
<tbody>
</tbody>
