Using formulas to display data in one sheet on another sheet

I think it is possible, but some points are not clear.

1. What should be displayed on sheet 2 if no topic in the row is checked?
2. What should be displayed on sheet 2 if multiple topics in the row?
3. Does it need to check the names are exactly same on both tables? ( In other words, does the formula for table 2 need to search the row from the data in its column A and B?)

Anyway, here is one idea with reduced data set.

# not checking all possible situation.

On current Assignment Table,

Column C: get the row number relative to first data row.

C2: =MATCH(A2&","&B2,Master::C,0)−ROW(Master::D$4)

Column D: get the column number with first data column as 1.

D2: =IFERROR(MATCH(TRUE,OFFSET(Master::$D$4,C2,0,1,10),0),0)

After filling up data, you can hide column C.

RiM/CiM is just my note.

• RiM - Row in Master
• CiM - Column in Master

Here is the updated version with your answers,

With answer 3, the data used for name lookup are removed from both tables.

# Now columns are increased to include 53 data columns.

This makes C2 as

=IFERROR(MATCH(TRUE,Master::$C4:$BC4,0),"")

This searches for the first checked cell (TRUE) in the specified cells ( Master::$C4:$BC4 ) and returns the position in the referred cells, or "" (blank string) if not found.

Please note that the number at this point is just the relative position in the referred cell ( Master::$C4:$BC4 ) and has no relation to the value in the 3rd row in Master table.

If you need to look up the actual value in the row, C2 will be

=IFERROR(INDEX(Master::$C$3:$BC$3,1,MATCH(TRUE,Master::$C4:$BC4,0)),"")

For C3 and below, you can use autofill by pulling down the yellow point shown in the figure above.

Edit:

To increase data column to 53 with the formula in the previous post, change 10 to 53.

D2: =IFERROR(MATCH(TRUE,OFFSET(Master::$D$4,C2,0,1,53),0),0)