Generate new table from a current table in excel

Hi, I have a sample table like this:

ID	1	2	3
1	0	1	0
2	1	1	1

Then I want to generate a new table from that table. It will take the second row (1) then compares with each column (1, 2, 3) then prints the value of the matrix ( 0 - 1 - 0 ). For example:

Row_ID	Column_ID	Value
1	1	0
1	2	1
1	3	0
2	1	1
2	2	1
2	3	1

I'm not sure how or where to start by using formula. Please help. Thanks,

Hi -

I would use INDEX/MATCH for this. Assuming your data table is in cells L11:O13, Row_ID starts in cell M4, and Column_ID starts in N4, copy and paste this formula into O4 and copy down:

=INDEX($L$11:$O$13,MATCH(M4,$L$11:$L$13,0),MATCH(N4,$L$11:$O$11,0))

Hope this helps.

Great! Glad I could help!