Formula or code?

[TGGarmoEX]

Hi guys n gals, I’m struggling and could use a bit of help please. I’m not sure if it can be done through Vlookup() etc, or needs to be a bit of code.

Sheet 1 I have a list of CLIENTS; their GEOGRAPHICAL LOCATION; STAFF ALLOCATED TO THE CLIENT and an empty column where I want the AREA THE STAFF WORK FROM filled.
To do this I want SHEET 1 to look at SHEET 2 and where the 'staff name' are a match, fill the corresponding AREA THE STAFF WORK FROM in column E.
We have a lot of ‘comings and goings’ so the idea here is that it saves me time when it comes to allocating staff to clients.
I hope i’ve explained it okay.

 

[sandy666]

you can try Power Query

let
    Source = Table.NestedJoin(Table1,{"STAFF ALLOCATED TO THE CLIENT"},Table2,{"STAFF ALLOCATED TO THE CLIENT"},"Table2",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table2", {"AREA THE STAFF WORK FROM"}, {"AREA THE STAFF WORK FROM"})
in
    Expand

 

[TGGarmoEX]

Hi Sandy, thank you for the speedy reply. Can you let me know how I add the code please ?

 

[AhoyNC]

Welcome to the forum.
Try:

=VLOOKUP(D2,Sheet2!$A$2:$A$26,2,0)

 

[sandy666]

what is your excel version? (update your profile about that, Account details)

 

[TGGarmoEX]

I am using Excel for Mac version 16.16.20 however I use a number of different versions on different computers.

 

[TGGarmoEX]

Welcome to the forum.
Try:

=VLOOKUP(D2,Sheet2!$A$2:$A$26,2,0)

Thank you, I live in astonishment how knowledgeable the contributors on here are!
I got the error #REF

 

[sandy666]

Mac version 16.16.20

that is why update your profile about excel version and OS
you can see if this will work for you on Mac version

s666-PQ-formula or code.xlsx

Microsoft Excel Workbook

1drv.ms

 

[AhoyNC]

The VLOOKUP works for me using the data that sandy666 put in for you (thank you Sandy666 - I wasn't about to type in all that data).
If you are getting an #REF error make sure the the names in both sheets are an exact match.

Sheet1

Book1
	A	B	C	D	E
1	CLIENTS NAME	CLIENTS CUSTOMER NUMBER	GEOGRAPHICAL LOCATION CODE	STAFF ALLOCATED TO THE CLIENT	Area
2	Betasoloin	A1300CA	S-01-031	Tricia Lane	CS
3	Betatech	A1296EE	E-01-027	Kurt Smith	D
4	Bioholding	A1293EE	C-01-024	Sophie Mason	A
5	Bioplex	A1275	S-01-031	Terry Francis	B
6	Blackzim	A1285	CS-01-041	Tricia Lane	CS
7	Cancity	A1282	STE-01-038	Jeannette Powell	S.C
8	Codehow	A1265	C-01-046	Reginald Holland	C
9	Condax	A21678CH	D-01-025	Marilyn Moore	D
10	Conecom	A1268	C-01-024	Brett Ramirez	F
11	Dalttechnology	A1278	B-01-034	Kurt Smith	D
12	dambase	A1263CT	CS-01-044	Darrel Summers	A
13	Domzoom	A1297DD	S-01-028	Brett Ramirez	F
14	Doncon	A1315CA	C-01-046	Dorothy Frazier	HCC
15	Donquadtech	A12S2CA	A-01-033	Sophie Mason	A
16	Dontechi	A1286	D-01-042	Dorothy Frazier	HCC
17	Don ware	A12S4CA	B-01-035	Judith Larson	C
18	Fasehatice	A1288	CS-01-044	Myron Mccoy	B
19	Faxquote	A125SBT	C-01-036	Kurt Smith	D
20	Finhigh	A1283	B-01-039	Delbert Harrison	B
21	Finjob	A1306EE	F-01-037	Lorenzo Hunter	cs
22	Funholding	A1309DD	HCC-01-040	June Thompson	F
23	Ganjaflex	A1295EE	D-01-026	Lorenzo Hunter	cs
24	Gogozoom	A1249CA	G-01-030	Lorenzo Hunter	cs
25	Golddex	A124SBT	E-01-027	June Thompson	F
26	Goods il ron	A4295CH	C-01-024	Orville Morris	C
27	Green-Plus	A1272	S-01-028	Jeannette Powell	S.C
28	Groovestreet	A1273CA	HCC-01-029	Sophie Mason	A
29	Hatfan	A1289	SC-01-045	Kurt Smith	D
30	Hottechi	A1294EE	D-01-025	Terry Francis	B
31	inity	A1291CA	A-01-022	Olga Welch	ST
Sheet1

Cell Formulas
Range	Formula
E2:E31	E2	=VLOOKUP(D2,Sheet2!$A$2:$B$26,2,0)

Sheet2

Book1
	A	B
1	STAFF ALLOCATED TO THE CLIENT	AREA THE STAFF WORK FROM
2	Boyd Fernandez	STE
3	8rendan Hudson	ST
4	Brett Ramirez	F
5	Darrel Summers	A
6	Delbert Harrison	B
7	Dora Singleton	STE
8	Dorothy Frazier	HCC
9	Erma Haynes	E
10	Fred Bradley	HCC
11	Jeannette Powell	S.C
12	Jonathon Woods	A
13	Judith Larson	C
14	June Thompson	F
15	Kurt Smith	D
16	Lillie Meyer	S.C
17	Lorenzo Hunter	cs
18	Marilyn Moore	D
19	Myron Mccoy	B
20	Olga Welch	ST
21	Orville Morris	C
22	Reginald Holland	C
23	Ronnie Adkins	E
24	Sophie Mason	A
25	Terry Francis	B
26	Tricia Lane	CS
Sheet2

 

[TGGarmoEX]

I don’t know what i did before but it works a treat! Thank you both so much, I appreciate your help!