Finding matches and adding additional data

[hammer2]

Greetings from down under!

Sheet 2 in the attached sample workbook (without all the pre-existing formula's from the original) contains data that I paste in (Cols A to D).
Sheet 1 then searches that data (minus Col D) from Sheet 2 and returns "Yes" to Col I if matching data is found.

What I would like to do now is if Sheet 1 returns "Yes" then the corresponding value from Sheet 2 would be returned to Sheet 1 Col J.

So in this example the value "5" would be returned to Sheet 1 Cell J1

I already have all the formulas in place to find the matches and return "Yes", this is just to return the additional information from Col D Sheet 2 to Sheet 1 Col J

Hope that makes sense.
Cheers
Hammer

[cffndncr]

Enter this formula in J1

{=IF(Sheet2!$A:$A=Sheet1!$C1,IF(Sheet2!$B:$B=Sheet1!$D$1,Sheet2!$D:$D,""))}

It's an array formula, so you need to enter it using ctrl+shift+enter

B2 and C2 in your example were the same, so I only included one in the criteria.

[hammer2]

Many thanks cffndncr
It is now up and running.
Note: As I will be entering multiple lines to sheet 2 at the one time I removed the $ from the formula and it pulled down okay without using it as an array formula. It is working so I think that is okay.

Cheers
Hammer

[cffndncr]

Glad it's all worked out for you

If this takes care of your problem, please mark the thread as [Solved]