formula to pull back a value depending on multiple conditions

Question

Hi all,

I have 2 datasets.

• if column 1 in dataset 1 is W21410 5686
• , and if column 1 in data set 2 is WC21417 R5836
• and if both data sets that satisfy the above 2 bullet points has the same reference number ( column 2),
• i want a formula to pull back what is in column 3 in dataset 2 into column 3 in dataset 1.
• ie. for line 3 in dataset 1, the formula should pull back 28,127 in column 3

what would be the most efficient formula to do this please?

DATA SET 1

column 1	Column 2	Column 3
W21417 5689	2790308
W21417 5689	30174542
W21417 5689	50000234
W21405 5686	70052966
W21410 5686	50073033
W21417 5689	50007281

DATA SET 2

column 1	Column 2	Column 3
WC21630 R5838	8572180	7,992
WC21630 R5838	8572180	7,992
WC21417 R6103	30412027	14,420
WC21417 R5836	2790308	1,517
WC21417 R5836	30174542	24,349
WC21417 R5836	50000234	28,127

Answer 1

=IFERROR(IF(A2="W21417 5689",FILTER(Sheet1!C2:C7,(Sheet1!A2:A7="WC21417 R5836")*(Sheet1!B2:B7=B2)),""),"")

Or

=IF(A2="W21417 5689",XLOOKUP(1,(Sheet1!A2:A7="WC21417 R5836")*(Sheet1!B2:B7=B2),Sheet1!C2:C7,""),"")

Answer 2

Doesn't work unfortunately as I just want to pull back a value in column 3 if column 1 in dataset 1 equals W21417 5689 and column 1 in dataset 2 equals WC21417 R5836

Answer 3

=XLOOKUP(B2,Sheet2!B:B,Sheet2!C:C,"")