|Since we now know that Column A may contain different values than shown in your examples, it sounds to me like we first need to lookup the combination of A11 and B11 and find the Row that contains the matching set of values in Table 1 Columns A & B.|
After that, we need to lookup the value found in C11 in C1:E1 and see which Column it is in.
We can then use the Row number and Column number with the INDEX function to return the value at that "intersection".
=INDEX(array, row_num, column_num)
Here's what I would do:
I would use another column (e.g. F) and Concatenate the values in Table 1 Columns A and B. You can use any column you want and then hide it if you don't want to see it.
For example, in F2 enter this and then drag it down to F4:
This will give you values like Lab10, Oper25, etc.
Then, assuming you used F2:F4 for your concatenated strings, use this formula in D11:
The first MATCH function will lookup the combination of A11 and B11 in F2:F4 and return the Row number of the array where it was found. Note: The value that is returned is not the Row number of the Excel spreadsheet, but the Row number of the array F2:F4. i.e. F2 is Row 1, F3 is Row 2, etc.
The second MATCH function will lookup the value in C11 in C1:E1 and return the Column number of the array where it was found. Again, that's the Column number of the array C1:E1, not the column number of the spreadsheet. i.e. Column C is Column 1, etc.
Finally, the INDEX function will use the Row and Column numbers returned by the MATCH functions to return the value found at that intersection within the array C2:E4.
For example, if the MATCH functions returned Row 1 and Column 2, INDEX would return the value found in D2, since that is the cell at the intersection of Row 1 and Column 1 of the array C2:E4.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.