look up with multiple criteria

[heidiau]

Sheet 1 full list
Sheet 2 list of student taking more than one type of lessons

I want to show the lesson time in sheet 2 column c. The criteria are sheet 2 column a and b. I want the formula can be copied down to the last row. How can I do that?

Some of the students having double sessions for the same instrument will have two individual entries, so remove duplicate cannot suit my needs.

Thank you.

[FDibbins]

Try this in sheet2 A2, copied down and across as needed...
=IFERROR(INDEX(Sheet1!A$2:A$5,MATCH(Sheet1!$A2&Sheet1!$B2,INDEX(Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,0),0)),"")

[heidiau]

it doesn't work. Instrument is returned instead of time.

[heidiau]

I resolved the problem: {=INDEX($A$2:$C$5, MATCH(1, ($B$2:$B$5=A19)*($A$2:$A$5=B19), 0), 3)}

Thank you anyways.

[FDibbins]

it doesn't work. Instrument is returned instead of time.

I used the wrong range, sorry - I thought they were in the same sequence.

Use this regular formula instead...
=IFERROR(INDEX(Sheet1!$A$2:$C$5,MATCH(Sheet1!$A2&Sheet1!$B2,INDEX(Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,0),0),MATCH(A$1,Sheet1!$A$1:$C$1,0)),"")
copied down and across