Excel match and find data from two set of column

[yghodiwala]

Hi all I am looking for formula which check the match from sheet 1 first two column with sheet two first two column and once the match is found it display the value of third from sheet 1 into the third colum of sheet 2.

sheet 1
1abc A123 Fun10001
2def B456 Fun10002
3ghi C789 Fun10003
4jkl D101 Fun10004
5mnoE234 Fun10005
6pqr F567 Fun10006
7stu G891 Fun10007
8vwx H234 Fun10008
9yza I567 Fun10009
10bcdG890Fun10010

sheet 2

7stu G891
8vwx H234
10bcdG890
1abc A123
2def B999
3ghi C999
4jkl D101
5mnoE234
6pqr F567
9zzz I567

The example is if 1abc and A123 from sheet 1 is found in sheet 2 then the value of column three sheet 1 Fun10001 to display in column three, row 4 in sheet 2.

Any help with formula is highly appreciated.

Cheers Yghodiwala

[ganeshkumar]

Hi,
please attach your file.....

[FDibbins]

HI, welcome to the forum

Sheet1...

	A	B	C
1	1abc	A123	Fun10001
2	2def	B456	Fun10002
3	3ghi	C789	Fun10003
4	4jkl	D101	Fun10004
5	5mno	E234	Fun10005
6	6pqr	F567	Fun10006
7	7stu	G891	Fun10007
8	8vwx	H234	Fun10008
9	9yza	I567	Fun10009
10	10bcd	G890	Fun10010

Sheet2...

	A	B	C
1	7stu	G891	Fun10007
2	8vwx	H234	Fun10008
3	10bcd	G890	Fun10010
4	1abc	A123	Fun10001
5	2def	B999
6	3ghi	C999
7	4jkl	D101	Fun10004
8	5mno	E234	Fun10005
9	6pqr	F567	Fun10006
10	9zzz	I567

C1=IFERROR(INDEX(Sheet1!$C$1:$C$10,MATCH(Sheet2!A1&" "&Sheet2!B1,Sheet1!$A$1:$A$10&" "&Sheet1!$B$1:$B$10,0)),"")

this an ARRAY formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[yghodiwala]

Hi Ford much appreciated your help.

Many Thanks,
YGhodiwala

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)