excel how to use index and match to match the data

Question

Morning,

My fault, let me converted to English.

My concern is that I do not know how to connect table_1's category with the table_2's column and then found the correct color

Answer 1

Please try this formula, to be entered in row 3 where columns A and B are "category" and "Sets" respectively.

=VLOOKUP(B3,E1:H4,MATCH(A3,E1:H1,0),FALSE)

E1:H4 represents your entire table2 incl captions. You can exclude the caption row - probably better. E1:H4 is the first row of that same range. There is presumed to be only one caption row which is needed, and must be 1 only. If you prefer a neater solution take the data column captions only and adjust the result. Then your formula might look like this.

=VLOOKUP(B2,E2:H4,MATCH(A2,F1:H1,0)+1,FALSE)

Answer 2

You need to use either the VLOOKUP or HLOOKUP feature when structuring your results table. For instance in cell C3 (color for head/set1) you would type =HLOOKUP(a2,e2:h5,2,FALSE). It will look horizontally in the first row for whatever is in cell A2 (HEAD) and return the value from the 2nd row (RED). Of flip it around to use HLOOKUP.
https://support.office.com/en-us/article/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f