Index Match question

[sunrise06]

I have one spreadsheet that has Last Name, First Name in one cell. I have another spreadsheet where I have ID#, Last Name, Middle Initial, First Name each in separate cells.

I would like to use INDEX and MATCH formulas to bring over ID# to the first spreadsheet. Is this possible?

Thanks!

 

[Scott T]

If you have unique combination of last and first name then you should be able to use index match to do that. If there is a duplicate then match will return the first match it find.

Book1
	A	B	C
1	last name	first name	id#
2	lname1	fname1	1
3	lname2	fname2	2
4	lname3	fname3	3
5	lname4	fname4	4
6	lname5	fname5	5
7	lname6	fname6	6
Sheet1

Cell Formulas
Range	Formula
C2	{=INDEX(Sheet2!A2:A7,MATCH(Sheet1!A2&Sheet1!B2,Sheet2!B2:B7&Sheet2!C2:C7,0))}
C3	{=INDEX(Sheet2!A3:A8,MATCH(Sheet1!A3&Sheet1!B3,Sheet2!B3:B8&Sheet2!C3:C8,0))}
C4	{=INDEX(Sheet2!A4:A9,MATCH(Sheet1!A4&Sheet1!B4,Sheet2!B4:B9&Sheet2!C4:C9,0))}
C5	{=INDEX(Sheet2!A5:A10,MATCH(Sheet1!A5&Sheet1!B5,Sheet2!B5:B10&Sheet2!C5:C10,0))}
C6	{=INDEX(Sheet2!A6:A11,MATCH(Sheet1!A6&Sheet1!B6,Sheet2!B6:B11&Sheet2!C6:C11,0))}
C7	{=INDEX(Sheet2!A7:A12,MATCH(Sheet1!A7&Sheet1!B7,Sheet2!B7:B12&Sheet2!C7:C12,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C
1	id#	last name	first name
2	1	lname1	fname1
3	2	lname2	fname2
4	3	lname3	fname3
5	4	lname4	fname4
6	5	lname5	fname5
7	6	lname6	fname6
Sheet2

 

[sunrise06]

Thank you for this however on Sheet 1 I only have Last Name, First Name in just once cell.

 

[Scott T]

Can you post a sample of your data (both sheets)?

 

[sunrise06]

Book1
	A	B
1	Name	ID# Lookup
2	Smith, John
3	Brown, Charlie
4	Sample, Sam
Sheet1

Book1
	A	B	C
1	ID#	Last	First
2	101	Smith	John
3	102	Brown	Charlie
4	103	Sample	Sam
Sheet2

 

[Scott T]

Something like should work. However in order to get a match the names need to be the same so if you have Smith, John in sheet one but Smith John M. in sheet 2 a match would not be found. The names in sheet two must match sheet one when put in the lastname, firstname format.

Book1
	A	B
1	Name	ID# Lookup
2	Smith, John	101
3	Brown, Charlie	102
4	Sample, Sam	103
Sheet1

Cell Formulas
Range	Formula
B2	{=INDEX(Sheet2!$A$2:$A$4,MATCH(Sheet1!A2,Sheet2!$B$2:$B$4&", "&Sheet2!$C$2:$C$4,0))}
B3	{=INDEX(Sheet2!$A$2:$A$4,MATCH(Sheet1!A3,Sheet2!$B$2:$B$4&", "&Sheet2!$C$2:$C$4,0))}
B4	{=INDEX(Sheet2!$A$2:$A$4,MATCH(Sheet1!A4,Sheet2!$B$2:$B$4&", "&Sheet2!$C$2:$C$4,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C
1	ID#	Last	First
2	101	Smith	John
3	102	Brown	Charlie
4	103	Sample	Sam
Sheet2

 

[sunrise06]

Great, thank you!

 

[sunrise06]

So I was able to get this to work for my specific situation. The example I provided you was just a test. What I am finding interesting is that if I sort my sheet 1, the lookup values all change. It seems the lookup number value in the match statement is not refreshing to the new row once sorted. In other words, if I have "MATCH(Sheet1!A101" in row 101 but sort so that it is now in row 2, "MATCH(Sheet1!A101" does not update to say "MATCH(Sheet1!A2".

Any ideas?