VLookup & Index or Match

[kizzie37]

I have a sheet (Worker) with Emp Id (Column A) then the employee's manager name (Column M), then I have a reference sheet (sheet 1 ) with a table of Manager names and Department Names (Column P - Manager Name & column Q - Department Name).

What I want is a formula to go into another sheet (Misc Info Column E) to first look up the emp ID in the "Worker" sheet and find out the manager name, then look up the manager name in "sheet 1" table and return the department name.

I am not sure how to compile the formula. Can anyone help?

 

[etaf]

where do you want the dept name added to - you mention a few sheets

I have a sheet (Worker) with Emp Id (Column A) then the employee's manager name (Column M),

as you already have the managers name here - you could just lookup the dept

Not sure why you are looking up the employee ID when its in the sheet worker

maybe i'm missing understanding what you want
Worker : = EMP ID , MANAGERS NAME
SHEET1 :- Manager Name, Dept
Misc Info: whats in here - column E is where you want the Dept returned - what info is in here ? -- is it just the Employee name

Anyway - I suspect a index/match will be what you are after - probably nested

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[kizzie37]

where do you want the dept name added to - you mention a few sheets

as you already have the managers name here - you could just lookup the dept

Not sure why you are looking up the employee ID when its in the sheet worker

maybe i'm missing understanding what you want
Worker : = EMP ID , MANAGERS NAME
SHEET1 :- Manager Name, Dept
Misc Info: whats in here - column E is where you want the Dept returned - what info is in here ? -- is it just the Employee name

Anyway - I suspect a index/match will be what you are after - probably nested

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

I am restricted from using the XL2BB and I cant trust the file share, the best I can do is below:

 

[etaf]

=INDEX($F$2:$F$4,MATCH(INDEX($B$2:$B$11,MATCH(I2,$A$2:$A$11,0)),$E$2:$E$4,0))

I have put in the same sort of format

so you just need to change the range and add the sheet names

I'll look at that later and maybe put on dropbox

=INDEX($F$2:$F$4,MATCH(INDEX($B$2:$B$11,MATCH(I2,$A$2:$A$11,0)),$E$2:$E$4,0))

here is the XL2BB version

Book1
	A	B	C	D	E	F	G	H	I	J
1	empID	manager name			Manager Name	Segment			EmpId	Segment
2	123456-00	joe bloggs			joe bloggs	MI5			123456-00	MI5
3	123456-01	joe bloggs			Bob Smith	UB40			123456-01	MI5
4	123456-02	joe bloggs			Harry H	Treasury			123456-02	MI5
5	123456-03	Bob Smith							123456-03	UB40
6	123456-04	Bob Smith							123456-04	UB40
7	123456-05	Bob Smith							123456-05	UB40
8	123456-06	Bob Smith							123456-06	UB40
9	123456-07	Harry H							123456-07	Treasury
10	123456-08	Harry H							123456-08	Treasury
11	123456-09	Harry H							123456-09	Treasury
12										#N/A
13										#N/A
14										#N/A
15
16
17	WORKER				Sheet 1				Misc Info
Sheet1

Cell Formulas
Range	Formula
J2:J14	J2	=INDEX($F$2:$F$4,MATCH(INDEX($B$2:$B$11,MATCH(I2,$A$2:$A$11,0)),$E$2:$E$4,0))

 

[etaf]

i have made the different sheets and used
=INDEX('Sheet 1'!$B$2:$B$4,MATCH(INDEX(Worker!$B$2:$B$11,MATCH(A2,Worker!$A$2:$A$11,0)),'Sheet 1'!$A$2:$A$4,0))

you can add a IFERROR - to stop the error message if cannot be found

=IFERROR(INDEX('Sheet 1'!$B$2:$B$4,MATCH(INDEX(Worker!$B$2:$B$11,MATCH(A2,Worker!$A$2:$A$11,0)),'Sheet 1'!$A$2:$A$4,0)),"")

If the names are not exact - it will not match

change range to match your data ranges

Usually excel uses sheet1 - no space - but you have a space Sheet 1 - so i used that

dropbox - will only be on the share for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

XL2BB versions of each sheet

nested Index - ETAF.xlsx
	A	B
1	EmpId	Segment
2	123456-00	MI5
3	123456-01	MI5
4	123456-02	MI5
5	123456-03	UB40
6	123456-04	UB40
7	123456-05	UB40
8	123456-06	UB40
9	123456-07	Treasury
10	123456-08	Treasury
11	123456-09	Treasury
12		#N/A
13		#N/A
14		#N/A
Misc Info

Cell Formulas
Range	Formula
B2:B14	B2	=INDEX('Sheet 1'!$B$2:$B$4,MATCH(INDEX(Worker!$B$2:$B$11,MATCH(A2,Worker!$A$2:$A$11,0)),'Sheet 1'!$A$2:$A$4,0))

nested Index - ETAF.xlsx
	A	B
1	Manager Name	Segment
2	joe bloggs	MI5
3	Bob Smith	UB40
4	Harry H	Treasury
Sheet 1

nested Index - ETAF.xlsx
	A	B
1	empID	manager name
2	123456-00	joe bloggs
3	123456-01	joe bloggs
4	123456-02	joe bloggs
5	123456-03	Bob Smith
6	123456-04	Bob Smith
7	123456-05	Bob Smith
8	123456-06	Bob Smith
9	123456-07	Harry H
10	123456-08	Harry H
11	123456-09	Harry H
Worker

 

[Automatrix]

Since you have Excel 365, you can take advantage of the new XLOOKUP, which rolls INDEX, MATCH and IFERROR into one formula. You may also benefit from arranging your data in named tables to avoid having to manually update data ranges.

Book3
	A	B	C	D	E	F	G	H	I	J
1	empID	manager name			Manager Name	Segment			EmpId	Segment
2	123456-00	joe bloggs			joe bloggs	MI5			123456-00	MI5
3	123456-01	joe bloggs			Bob Smith	UB40			123456-01	MI5
4	123456-02	joe bloggs			Harry H	Treasury			123456-02	MI5
5	123456-03	Bob Smith							123456-03	UB40
6	123456-04	Bob Smith							123456-04	UB40
7	123456-05	Bob Smith							123456-05	UB40
8	123456-06	Bob Smith							123456-06	UB40
9	123456-07	Harry H							123456-07	Treasury
10	123456-08	Harry H							123456-08	Treasury
11	123456-09	Harry H							123456-09	Treasury
Sheet4

Cell Formulas
Range	Formula
J2:J11	J2	=XLOOKUP(XLOOKUP([@EmpId],Table1[empID],Table1[manager name]),Table2[Manager Name],Table2[Segment])