help with matching data

[arey]

Hello all,

Does anyone know how I can match an item in a row with another row?

I have a row of data A2:J2. A persons name could be in any column in that row.

I have another two rows of data where the peoples name and department name appear Where L2:L7 = peoples names
where M2:M7 = peoples department


I want to match a name in A2:J2 with one in L2:L7 and if matched put the department name in K2. Then obiviously i will copy the formular down column K.

Any help would be great.

 

[texasalynn]

try this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1				NameDept
2				arey
3				finance
Sheet2

Excel Workbook
	L	M
1
2
3	Carol	Purchasing
4	james	Sales
5	arey	finance
6	Tom	Top Dog
7
8
Sheet2

Hope I got the layout right - HTH

 

[arey]

thanks. THis looks like it should wokr but i get a #value! error.

Any ideas?

 

[texasalynn]

post a sample of your data with Excel Jeanie HTML 4 link in previous post

 

[arey]

how do i post the example?

 

[texasalynn]

like I said in the prior post, use the Excel Jeanie, there is a link in the post #2

 

[arey]

I cant seem to post a screen shot of the table on the site you suggested.
Can you tell me how to do that?

 

[arey]

Heres the example you wanted...

http://www.mrexcel.com/forum/showthread.php?t=380134&goto=newpost

 

[arey]

heres the image..

F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

AE2 =


AE AF AG AH AI AJ AK AL AM AN AO AP AQ
1 Group Manager Name Manager ID 2nd Level Manager 2nd Level Mgr ID 3rd Level Manaager 3rd Level MGR ID 4th Level Manager 4th Level Mgr ID Department head ID Deapartment Head Group
2 #VALUE! John Cherian 006986 Umesh Rege 005768 Johnny Mathis 002976 George Jetson 900320 007248 George Jetson Strategic Initiatives
3 Krzysztof Supel 007481 Matthew Taylor 006985 Yogi Bear 002976 Yogi Bear 900320 009809 Yogi Bear US Futures Trading Business
4 Charles Hackett 003435 Gerald Casson 004280 Sammy Sozzoa 004158 Brian Adams 008530 009438 Boo Boo Global Human Resources
5 Andrew Richman 006277 Holly Kulka 009129 Betty Ruble 009429 Dupty Dog 008797 007753 Dupty Dog Global Affairs and Gov Relations
6 Anthony Loffredo 900666 Terry Pruitt 901464 Paul Mall 802565 Tom Jones 801846 009772 Kazoo Corporate Finance
7 Patricia Bergholc 007749 Susan Lui-Facendola 007542 Johnny Mathis 007432 James Dean 004790 009429 Willima Flintstone General Councel
8 Cynthia Melo 008438 Janice O'Neill 005578 James Dean 004790 005047 Betty Rumble Global Listings
9 Charles Hackett 003435 Gerald Casson 004280 Sammy Sozzoa 004158 Brian Adams 008530 009096 Barney Rumble US Markets and Global Technology
10 Kevin Royce 005919 Dean Laumbach 005458 Sammy Sozzoa 004158 Brian Adams 008530 007253 Bam Bam Rumble Corp Strtgy and Bus Dev
11 Walter Thomas 901143 Michael Stancampiano 005060 Speed Racer 802667 Tom Jones 801846 700040 Pebbles Flintstone Corporate Communications
12 Bernadette Russell 003839 Matthew Taylor 006985 Peter Brady 002976 Billy Idiol 900320
13 Thomas O'Neill 932651 Pasquale Antonacci 932870 Sammy Sozzoa 004158 Brian Adams 008530
people

 

[arey]

I think i got it this time. Here is a snapshot

test_for_match.xls
	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	Group	Manager Name	Manager ID	2nd Level Manager	2nd Level Mgr ID	3rd Level Manaager	3rd Level MGR ID	4th Level Manager	4th Level Mgr ID		Department head ID	Deapartment Head	Group
2	#VALUE!	John Cherian	006986	Umesh Rege	005768	Johnny Mathis	002976	George Jetson	900320		007248	George Jetson	Strategic Initiatives
3		Krzysztof Supel	007481	Matthew Taylor	006985	Yogi Bear	002976	Yogi Bear	900320		009809	Yogi Bear	US Futures Trading Business
4		Charles Hackett	003435	Gerald Casson	004280	Sammy Sozzoa	004158	Brian Adams	008530		009438	Boo Boo	Global Human Resources
5		Andrew Richman	006277	Holly Kulka	009129	Betty Ruble	009429	Dupty Dog	008797		007753	Dupty Dog	Global Affairs and Gov Relations
6		Anthony Loffredo	900666	Terry Pruitt	901464	Paul Mall	802565	Tom Jones	801846		009772	Kazoo	Corporate Finance
7		Patricia Bergholc	007749	Susan Lui-Facendola	007542	Johnny Mathis	007432	James Dean	004790		009429	Willima Flintstone	General Councel
8		Cynthia Melo	008438	Janice O'Neill	005578	James Dean	004790				005047	Betty Rumble	Global Listings
9		Charles Hackett	003435	Gerald Casson	004280	Sammy Sozzoa	004158	Brian Adams	008530		009096	Barney Rumble	US Markets and Global Technology
10		Kevin Royce	005919	Dean Laumbach	005458	Sammy Sozzoa	004158	Brian Adams	008530		007253	Bam Bam Rumble	Corp Strtgy and Bus Dev
11		Walter Thomas	901143	Michael Stancampiano	005060	Speed Racer	802667	Tom Jones	801846		700040	Pebbles Flintstone	Corporate Communications
12		Bernadette Russell	003839	Matthew Taylor	006985	Peter Brady	002976	Billy Idiol	900320
13		Thomas O'Neill	932651	Pasquale Antonacci	932870	Sammy Sozzoa	004158	Brian Adams	008530
14		Billy Idiol	900320	Tom Jones	801846	Barney Rumble	009096	Fred Flintsone	008797
people