Excel Help

lozz

New Member
Joined
Aug 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am creating a new spreadsheet. I have two columns - 1) Department and 2) Manager. I have made a "List" for the department - such as HR, Recruitment, etc.

My plan is for when you select the department from the drop down list, the managers name automatically is populated under the 2nd column "Manager".

What forumla can i use for this to happen? I have created a new tab with the Department names and the Managers names against each one so that i can use this for any forumla i do...

any help and gudiance is appreciated.

Thank you.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Forum,

VLookup or Index and Match should do what you want.
 

lozz

New Member
Joined
Aug 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thank you.

So i have tried VLOOKUP but i feel i may be doing the formula wrong?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,744
Office Version
  1. 365
Platform
  1. Windows
Why don't you post your formula here, and explain the details of the data structure (i.e. what columns the values you are looking up are in, and what columns the values you want to return are found in)?
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Look at this

Book1
AB
1Dept NameManager
2ProductsAnne
Sheet1
Cell Formulas
RangeFormula
B2B2=IFERROR(VLOOKUP(A2,DeptMan,2,FALSE),"")
Named Ranges
NameRefers ToCells
DeptMan=Departments!$A$1:$B$5B2


On the Departments Sheet

Book1
AB
1Dept NameManager
2HRKeith
3SalesTony
4FinanceSarah
5ProductsAnne
Departments
 

lozz

New Member
Joined
Aug 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Pic1 - This is my main spreadsheet. I have a drop down list as you can see of the departments. I want to select "HR" for example, and for Joe Bloggs name to appear in column B,

Pic 2 - this is my second tab where the managers names are populated against each department so that I can use that info to transfer across to the main spreadsheet so that it auto-populates column B.

How do i populate the VLOOKUP formula?

Sorry it is probably really simple... I just havent used Excel in a long time. appreciate your help.
 

Attachments

  • Pic1.png
    Pic1.png
    11.8 KB · Views: 3
  • Pic2.png
    Pic2.png
    22.5 KB · Views: 3

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Very basic steps:
The sheet that holds the department names and Managers Names - Highlight the table and to the left of the formula bar is the name box tap it and type in DeptMan and press enter.
Now go to the sheet where you have your drop down and tap the cell below Manager. Copy the formula from my first reply and it should work. Then pull the formula down as far as you need to go.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,744
Office Version
  1. 365
Platform
  1. Windows
OK, for row 12, enter this formula in cell B12:
=IF(A12="","",VLOOKUP(A12,Sheet2!$A$2:$B$5,2,0))
and you should be able to copy this formula down for all rows.

Note, if the name of yous sheet that has these Managers on it is not named "Sheet2", just put the real name of the Sheet there instead.
If there are any spaces in the name, you will need single quotes around, i.t.
'Mgr Sheet'!$A$2:$B$5
 

lozz

New Member
Joined
Aug 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thank you so much!! It has worked.

Thank you for taking the time to explain this to me... i may be back with more questions relating to other excel bumps i hit LOL!
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Happy to help interestingly you will more than likely get different methods to find a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,130
Messages
5,628,871
Members
416,347
Latest member
AT2021

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
Top