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

A	B	C
Week1	Name1
Week1	Name2
Week2	Name1
Week2	Name2

<tbody>
</tbody>

Sheet 2

A	B	C
Week1	Name1	2
Week2	Name1	1
Week2	Name2	3

<tbody>
</tbody>

 

[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