Compared 2 conditions and copy value from another column

[pochitodiaz]

Hello,

I have an issue that I have been working on for a bit now, but I can't seem to figure it out. I have 2 sheets with lots of information on it. I want to compared the values from Sheet1 column A and column D to the values on Sheet2 Column A and column B and if they match copy Sheet2 Column C to Sheet1 Column C. The issue is that the data on Sheet2 is not always going to have the same values for column B. To clarify see tables below, Sheet 1 has all the names of the people that are part of my team (Name1 & Name2) and the week number, but Sheet2 will only have the name of the people that have a value to report for the specific week. I tried using
=IFERROR(INDEX(Sheet2!A:C,MATCH(B:B,Sheet2!B:B,0),3),0) but because Name2 is missing for week1 on sheet2 it returns the value of week2 to the first sheet which is incorrect. Is there a way to accomplish this?

Sheet 1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Week1[/TD]
[TD]Name1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week1[/TD]
[TD]Name2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week2[/TD]
[TD]Name1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week2[/TD]
[TD]Name2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Sheet 2
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Week1[/TD]
[TD]Name1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Week2[/TD]
[TD]Name1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Week2[/TD]
[TD]Name2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[pochitodiaz]

Hello,

I think I have finally figured it out.

=IFERROR(INDEX(Sheet2!A:C,MATCH(1,(Sheet2!A:A=A2)*(Sheet2!B:B=B2),0),3),0)

This formula is doing what I was trying to do. I am sure there is a lot of other ways to do it, but posting it here in case it helps someone in the future