Index and match

[cyee]

Hi Guru,

I trying to use Index and match for attached example but fail to show me the correct result.
Can someone help?

This is the formula i used.

Please Login or Register  to view this content.

[JohnTopley]

Try

=INDEX(Sheet1!C2:C21, MATCH(1, (Sheet2!B3=Sheet1!A2:A21) * (Sheet2!C3=Sheet1!B2:B21), 0))

BUT unmerge cells in column A: NEVER use merged cells when used by formulae

[wk9128]

worksheet name : Sheet2
Cell C4 array formula

[cyee]

Thank you so much both of you!!!!

[cyee]

worksheet name : Sheet2
Cell C4 array formula

Hi wk9128,

May i know what is ROW($1:$12) represent?
I would like to modify to add in 1 more march. Is following correct?

Please Login or Register  to view this content.

[wk9128]

Try

=INDEX(LOOKUP(ROW($1:$30),ROW($1:$30)/('Mth Contr'!$E$1:$E$30<>""),'Mth Contr'!$E$1:$E$30),MATCH(B6&B1&B2,LOOKUP(ROW($1:$30),ROW($1:$30)/('Mth Contr'!$A$1:$A$30<>""),'Mth Contr'!$A$1:$A$30)&'Mth Contr'!$B$1:$B$30)&'Mth Contr'!$C$1:$C$30,)

[cyee]

Thank you very much

[wk9128]

You're Welcome. Thank You for the feedback

[Liz C]

Not sure if I'm trying to do the same thing, but I think this is the closest I've found (though who knows?).

I want to assign a supervisor randomly to a student for different modules. I know how many students there are, how many supervisors and how many students each supervisor will work with. I have assigned ID numbers to students, and have randomised them to assign to the supervisors. Rather than having to manually add the supervisor name to the student can I get excel to do it for me. So:

Col A is student ID, Cols C - G show module name, Col I is supervisor and Col J is the assigned student ID number. If student ID in Col A matches student ID in Col J return the name shown in Col I to Col C.

Once I've done it for one module (Col C) I can probably work out how to replicate it for the remaining modules!

Cheers!!