help with a match

arey

Board Regular
Joined
Feb 14, 2006
Messages
166
Can anyone tell me what im doing wrong.
Im trying to match a name in AF2:AM2 with a name in AP2:AP11 and put the persons depart name in AQ2:AQ11 in cell AE2. Then i can copy the formula down..
test_for_match.xls
AEAFAGAHAIAJAKALAMANAOAPAQ
1GroupManager NameManager ID2nd Level Manager2nd Level Mgr ID3rd Level Manaager3rd Level MGR ID4th Level Manager4th Level Mgr IDDepartment head IDDeapartment HeadGroup
2#VALUE!John Cherian006986Umesh Rege005768Johnny Mathis002976George Jetson900320007248George JetsonStrategic Initiatives
3Krzysztof Supel007481Matthew Taylor006985Yogi Bear002976Yogi Bear900320009809Yogi BearUS Futures Trading Business
4Charles Hackett003435Gerald Casson004280Sammy Sozzoa004158Brian Adams008530009438Boo BooGlobal Human Resources
5Andrew Richman006277Holly Kulka009129Betty Ruble009429Dupty Dog008797007753Dupty DogGlobal Affairs and Gov Relations
6Anthony Loffredo900666Terry Pruitt901464Paul Mall802565Tom Jones801846009772KazooCorporate Finance
7Patricia Bergholc007749Susan Lui-Facendola007542Johnny Mathis007432James Dean004790009429Willima FlintstoneGeneral Councel
8Cynthia Melo008438Janice O'Neill005578James Dean004790005047Betty RumbleGlobal Listings
9Charles Hackett003435Gerald Casson004280Sammy Sozzoa004158Brian Adams008530009096Barney RumbleUS Markets and Global Technology
10Kevin Royce005919Dean Laumbach005458Sammy Sozzoa004158Brian Adams008530007253Bam Bam RumbleCorp Strtgy and Bus Dev
11Walter Thomas901143Michael Stancampiano005060Speed Racer802667Tom Jones801846700040Pebbles FlintstoneCorporate Communications
12Bernadette Russell003839Matthew Taylor006985Peter Brady002976Billy Idiol900320
13Thomas O'Neill932651Pasquale Antonacci932870Sammy Sozzoa004158Brian Adams008530
14Billy Idiol900320Tom Jones801846Barney Rumble009096Fred Flintsone008797
people
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Not sure exactly what you mean, but if you are trying to match just the Manager Name and pull the department use this:

=Index($AP$2:$AQ$11, match(AF2, $AP$2:$AP$11, 0), 2)

You could just use VLookup which is a little simpler.

=Vlookup(AF2, $AP$2:$AQ$11, 2, 0)

Hope that helps.
 
Upvote 0
yes im trying to match the manager name but there are several columns in which the managers name can appear not just in column AF. Thats where it gets tricky
 
Upvote 0
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

AE2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($AQ$2:$AQ$11,MATCH(TRUE,ISNUMBER(MATCH($AP$2:$AP$11,AF2:AM2,0)),0))

Hope this helps!

xl-central.com
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top