Can you use Vlookup against multiple locations?

[Oakey]

I have a spreadsheet that shows an opertives department, Clock number and name (As per below)

		Team 1			Team 2			Team 3			Team 4
DEPARTMENT	CLOCK NO	NAME	DEPARTMENT	CLOCK NO	NAME	DEPARTMENT	CLOCK NO	NAME	DEPARTMENT	CLOCK NO	NAME
Online Processors	110155	Cristina Dursan	Online Processors	200641	Adriana Lutsyk	Online Processors	200489	Abena Gyamfua Sule	Online Processors	110465	Iulia Cibric
Online Processors	200738	Ana Maria Emanuela Mihai	Online Processors	300226	Aleksandra Zavitovska	Online Processors	200558	Anastasiia Porollo	Online Processors	200566	Tatiana Godoroja
Online Processors	200663	Alexandra Mioara Balteanu	Online Processors	200454	Alexandra Onta	Online Processors	200510	Ayomide Raimot Adebavo	Online Processors	200248	Elena Ganea
Stores Processor			Stores Processor			Stores Processor			Stores Processor
Brands	200064	Efrosinia Foteniuc	Brands			Brands	110040	Aliona Rotaru	Brands	110634	Nicoleta Butacu
Brands	110009	Doina Muschei	Brands			Brands	110172	Violeta Lipan	Brands	110245	Silvia Petrici
Zara Home	110005	Valentina Stanilescu	Zara Home	110111	Doinita Butacu	Zara Home	110194	Diana Solodschi (+MOCA)	Zara Home	110236	Iryna Yavorska
Runner	110079	Victor Triboi	Runner	200515	Viorel Daia Collect GOH	Runner	200283	Milica Muraru	Runner	200365	Bobi Dumitru
Runner	200564	Gheorghe Ardeleanu	Runner	200276	Gheorghi Bulgaru	Runner	110739	Oleg Reznik	Runner	200577	Viorel Cretu

I have a 2nd spreadsheet that is a master list that has all the operatives.

Agency	Clock numbers	Name	SHIFT	TEAM	Allocated Department
SM	200489	Abena Gyamfua Sule	AM	Team 3	Online Processor
SM	200641	Adriana Lutsyk	AM	Team 2	Online Processor
SM	200442	Ala Ciobanu	AM	Team 2	Sewing
SM	200647	Ala Lapcinscaia	AM	Team 3	Online Processor
Tipngo	110025	Lidia Ursu	AM	Team 4	M location
SM	300226	Aleksandra Zavitovska	AM	Team 2	Online Processor
Tipngo	110086	Adriana Alexandru	PM	Team 7	Online Processor

What id like to be able to do is move an operative in the 1st sheet and it updates the department allocated on the 2nd spreadsheet

What vlookup formulas would i need to write?

Thanks for your help

 

[RobP]

Hi, not sure which version of Excel you have, but if you can use XLOOKUP() instead of VLOOKUP it could be a little easier. Also, fairly important to have your columns in the same order on both sheets, as it makes it easier to pull out several columns of data in one go. I based this one around the "clock no." as thats most likely to be unique between workers. Its quite possible that if you used "name" then you can have 2 employees with same name, and cause problems.

Anyway, heres a suggestion for you to get started with. I have 2 sheets, sheet1 and sheet2.

Book1
	A	B	C
1	Clock No	Name	Department
2	200489	Abena Gyamfua Sule	Online Processor
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=XLOOKUP(A2,Sheet2!A2:A20,Sheet2!B2:C20,"")
Dynamic array formulas.

Book1
	A	B	C	D	E	F
1	Clock Number	Name	Department	Agency	Shift	Team
2	200489	Abena Gyamfua Sule	Online Processor	SM	AM	Team 3
3	200442	Ala Ciobana	Sewing	SM	AM	Team 2
Sheet2

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)