Matching data in two sheets and then returning extra data

**hpofql** · 06-07-2010, 09:03 AM

Hi!

I have two sheets in a workbook. In Sheet 1 I need to match the data in Column G plus the data in Column F to the data in Column C in Sheet 2. Any matches then need to return the data that is in Column A on Sheet 2 to Column J on Sheet 1.

e.g.

Sheet 1
Column F - First Name
Column G - Surname
Column J - (need to insert ID from Sheet 2)

Sheet 2
Column A - ID number
Column B - [Not important]
Column C - Surname, First Name

So Column J in Sheet 1 needs to look up the data in Column G (Surname) then in Column F (First Name) in Sheet 1, then look for the same data in Column C (Surname, First Name) of Sheet 2 and then return the data in Column A Sheet 2 (ID Number) for those matches in Column J, Sheet 1 e.g. Sheet 1 Column F is 'Peter', Column G is 'Smith'; find 'Smith, Peter' in Sheet 2, Column C, and then look for his ID in Column A , Sheet 2. This ID number should then be returned to Column J in Sheet 1 (for same Peter Smith).

I hope that makes sense! I have no idea how to go about it though. I presume I need VLOOKUP but am stumped beyond that. Any help much appreciated!

**martindwilson** · 06-07-2010, 09:15 AM

is it always surname,lastname on sheet 2?
and no extra spaces
then try in j2
=INDEX(Sheet2!A:A,MATCH(G2&","&F2,INDEX(Sheet2!C:C,0),0))

**hpofql** · 06-07-2010, 09:37 AM

Yes, it is always surname, lastname in Column C on Sheet 2 but with a space between the comma and the lastname (eg. Smith, Peter)

Thank you for trying to help but, alas, this did not work. I haven't come across INDEX before so will look into this as it looks it might be useful.

**martindwilson** · 06-07-2010, 10:55 AM

ok put a space in after the comma in this bit G2&", "&F2 (ps where is Englad)

**hpofql** · 06-07-2010, 11:06 AM

Thank you so much! That worked. I'll have a good look at that and see if I can figure out what it is doing. Very much appreciated (and have corrected my typo, thank you).