Matching names to ID numbers

[dgavin]

I have two worksheets. Worksheet 1 and Worksheet 2.

Worksheet 1 lists first names in column A , last names in column B and ID numbers in column C.

Worksheet 1 in now out of date

Worksheet 2 contains an up to date list of first and last names but NO I numbers.

What formula can i use to enable Worksheet 2 find and match the same name as Worksheet 1 and copy the ID number into column C.

 

[Stormseed]

Assuming that your data starts in cell A1, one of the fastest way would be to add one more column in Sheet1 in say column D with the formula like:

=A1&" "&B1.

Copy column D and paste special values and finally do an INDEX(MATCH()) on column D to bring values in column C onto Worksheet 2.

Like this:

Worksheet1:

Book6
	A	B	C	D
1	Name	Name2	Number	ConcatenateA&B
2	Ram	Gam	222	RamGam
3	Sam	Jam	333	SamJam
4	Pam	Tam	444	PamTam
5
Sheet1

Worksheet2:

Book6
	A	B	C	D
1	Name	Number
2	SamJam	333
3
Sheet2

 

[Jubjab]

In cell A2 of sheet2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=Sheet2!A2),--(Sheet1!$B$2:$B$1000=Sheet2!B2),Sheet1!$C$2:$C$1000)

Copy and paste down.

 

[Stormseed]

In cell A2 of sheet2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=Sheet2!A2),--(Sheet1!$B$2:$B$1000=Sheet2!B2),Sheet1!$C$2:$C$1000)

Copy and paste down.

Whoa

Am I missing something here ? It can be that I did not understand the question:

Is Worksheet2 having first and last names in a single column (col A) or are the first and last names divided into 2 columns ( col A & col B ) respectively ?

This is the main reason I would like to probe the OPs before answering them...and I guess I need to be careful now

 

[Aladin Akyurek]

I have two worksheets. Worksheet 1 and Worksheet 2.

Worksheet 1 lists first names in column A , last names in column B and ID numbers in column C.

Worksheet 1 in now out of date

Worksheet 2 contains an up to date list of first and last names but NO I numbers.

What formula can i use to enable Worksheet 2 find and match the same name as Worksheet 1 and copy the ID number into column C.

Sheet2...

C2:

Control+shift+enter, not just enter...

=INDEX(Sheet1!$C$2:$C$300,MATCH(1,IF(Sheet1!$A$2:$A$300=A2,IF(Sheet1!$B$2:$B$300=B2,1)),0))

and copy down.