List Questions on variable match

[apgmin]

In Column b1, the student choses a subject from the list of 8 subject ( Done through, Data validation List )
Column b2, the student choses another subject from the list of the same 8 subject ( Done through, Data validation formula ). Both the subjects will never be the same

Column e has 20 questions for each of the 8 subjects,. ( A few are shown )

In Column M, I need a formula such that, B1=Physics, then E2 ; B1=Chemistry, then E7.
then a formula in M2, such that, B1=Physics, then E3 ; B1=Chemistry, then E8. and so on
I know how to populate Columns N to R

Thanks in Advance for your efforts

 

[Trebor76]

Hi apgmin,

The ranges will have to be tweaked for each block (subject) but try this for the first block i.e. M2:M6 which will show you how to do it:

=XLOOKUP(1,($D$2:$D$16=$L$1)*($C$2:$C$16=L2),$E$2:$E$16)

Regards,

Robert