Double Fields Lookup Issue

[sylink]

Please I have a need for a code to match 2 different fields against another.
Below is the sample data. The codes and names form unique ID and need to be matched together. Where match is obtained, the Reg. No is copied to sheet.

Sheet 1
Code Name
001 paul
003 saint
004 peter
=============

Sheet 2
Code Name Reg. No.
003 paul er4556
001 paul ty6550
004 peter hg6633
003 saint lp5566
005 cain lk2255


See sample result below.
Sample Result
=============
Sheet 1
Code Name Reg.No.
001 paul ty6550
003 saint lp5566
004 peter hg6633

 

[Claus Busch]

Hi,

Am Mon, 17 Sep 2012 07:03:41 -0700 (PDT) schrieb sylink:

Sheet 1
Code Name
001 paul
003 saint
004 peter
=============

Sheet 2
Code Name Reg. No.
003 paul er4556
001 paul ty6550
004 peter hg6633
003 saint lp5566
005 cain lk2255

See sample result below.
Sample Result
=============
Sheet 1
Code Name Reg.No.
001 paul ty6550
003 saint lp5566
004 peter hg6633

in C2 of Sheet1 try:
=INDEX(Sheet2!$C$2:$C$100,MATCH(A2&B2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch