(Please Help) Merge coloms based on equal rows with information

  • Thread starter Thread starter khers
  • Start date Start date
K

khers

Hi
I have a sheet with this information

Card# Name Surname
1 A Alast
2 B Blast
3 C Clast
4 D Dlast

and another sheet with this information

Student# Name Surname
22 B Blast
11 A Alast
33 C Clast
55 E Elast

I need to extract Card# and Student# by comparing Name and Surname and
get this list

Card# Student# Name Surname
1 11 A Alast
2 22 B Blast
3 33 C Clast

many thanks in advance
 
Add a formula to column D of Sheet1 and Sheet2 which concatenates the
names together, i.e. in D2 of both sheets:

=B2&C2

and copy this down as far as needed.

Then in Sheet3, assuming you have the names in columns C and D, put
this formula in A2:

=INDEX(Sheet1!A:A,MATCH(C2&D2,Sheet1!D:D,0))

and this in B2:

=INDEX(Sheet2!A:A,MATCH(C2&D2,Sheet2!D:D,0))

Then copy these down as far as required.

Hope this helps.

Pete
 
Back
Top