I want to be able to use one Vlookup to populate multiple columns. I think I can use an array formula, but I haven't found good explanation with a good breakdown. Any help would great!
I have attached a sample spreadsheet.
the look up is in cell A7 and I want to populate C7:H7 with employee ID's. In A7 would be the Vlookup for the manager's name and C7:H7 would be his employees.
Thanks!
A7 already has a drop down in it, so there won't be a VLOOKUP in that cell, too, it would just get erased the moment someone used the drop down to select a name.
So, a full example, please. If JEFF is selected in A7, what are the values that should appear in C7:H7?
If JASON is selected, what would the values be?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Currently, when I use the dropdown list, I have multiple variables that populate using the vlookup for that name. What I would like to do is select a managerL Jeff, John, or whom ever and those cells c7:H7 populate with their employee's id.
For example,
I select Jeff (manager) in A7, then In C7 I have employee 1 (dan), D7 employee 2 (Ray), etc. Does that make better sense? Then when I select John (manager) in A7 the same cells c7:h7 populate with John's employee's ids. I am not sure if I am explaining it properly.
Last edited by reynolds8481; 06-08-2015 at 10:59 PM.
Welcome to the forum
If you make the headings on each sheet the same (sheet1 has Employee: sheet2 just has employee pick 1), then you can use this...
=INDEX(Sheet2!$A$2:$D$7,MATCH(Sheet1!$A$7,Sheet2!$B$2:$B$7,0),MATCH(Sheet1!C$7,Sheet2!$A$1:$D$1,0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you for the help. My problem is that I have a 150 employees that continuously change due to turnover, new hires, or promotion and 10 managers. It sounds like that I may not be able to use the dropdown to select the manager and have a lookup to pul the current employee names for that manager. Could there be an alternate method to perform these actions? I don't know much about macros, but I am willing to learn!
Thanks again for the help!
Apart from the qty of names - why not?It sounds like that I may not be able to use the dropdown to select the manager and have a lookup to pul the current employee names for that manager.
lol, thats what I am trying to find out. Can I use the dropdown that I have, that will pull the names I need into a horizontal format from a master list of names like I would w/ an hlookup for a single entry?
Sounds like you only have 10 manager names? Thats a small enough list for a DD, and once you have that name, you can do all sorts of extraction stuff. vlookup and index/match will pull out 1 matching name (only) from a list, but something like index/small/if array can pull out multiple entries that match a specific criteria.
Show me what you want the answer to look like?
Where I have Jeff, would be the drop down with the multiple manager names. where the "emp id" would be where I would want the employee names to populate. Then based on those names I can pull the data needed for the metric fields.
jeff employee: "emp id" "emp id" "emp id" "emp id" "emp id"
metric 1 0 0 0 0 0
metric 2 0 0 0 0 0
metric 3 0 0 0 0 0
metric 4 0 0 0 0 0
metric 5 0 0 0 0 0
Did you try my formula from post #4?
sample.xlsxsample.xlsxWhere I have Jeff, would be the drop down with the multiple manager names. where the "emp id" would be where I would want the employee names to populate. Then based on those names I can pull the data needed for the metric fields. I have attached a sample xls also.
jeff employee: "emp id" "emp id" "emp id" "emp id" "emp id"
metric 1 0 0 0 0 0
metric 2 0 0 0 0 0
metric 3 0 0 0 0 0
metric 4 0 0 0 0 0
metric 5 0 0 0 0 0
Yes, I get that. Your sample only has one name per manager, so thats why I gave you that formula (did you try it...you didnt say?)Where I have Jeff, would be the drop down with the multiple manager names. where the "emp id" would be where I would want the employee names to populate. Then based on those names I can pull the data needed for the metric fields.
If you have more than 1 name per manager, then upload a sample showing that, and also show what you want
Yes, I get that. Your sample only has one name per manager, so thats why I gave you that formula (did you try it...you didnt say?)Where I have Jeff, would be the drop down with the multiple manager names. where the "emp id" would be where I would want the employee names to populate. Then based on those names I can pull the data needed for the metric fields.
If you have more than 1 name per manager, then upload a sample showing that, and also show what you want
The dropdown has more than one manager. There are 6, each of those managers will have 15 employees, I just have the 5 on the sheet for ease. I am trying that formula now, but I am not sure about naming a 150 sheets, finding, changing, removing, weekly. If I can use a lookup table or something that I can paste the updated roster, that would be best. That is what I currently do at an individual level for performance discussions. Now, I am trying to bring it to a manger level and review progress made on certain metrics.
Yes we have covered that part, not a prolem there.The dropdown has more than one manager.
Wait, where did the 150 sheets suddenly come from?but I am not sure about naming a 150 sheets,
I will ask again, please upload a small, representative sample workbook of what you have, and what you want
Maybe I am confused...
If you make the headings on each sheet the same (sheet1 has Employee: sheet2 just has employee pick 1), then you can use this...
=INDEX(Sheet2!$A$2:$D$7,MATCH(Sheet1!$A$7,Sheet2!$B$2:$B$7,0),MATCH(Sheet1!C$7,Sheet2!$A$1:$D$1,0))
I was thinking I would need a new sheet for each employee. The sample I uploaded is what I want it to look like. I want to select the manager from the dropdown and the employees name and/or id populates in the "emp id" cells. If you want to see the actually sheet I am working form I will have to scrub the info as the data is sensitive. Sorry if I am being a pain...Excel is obviously not my strong suit.
Thats ok, put a more realistic dummy filer together and send that. I will wait for that, so that we can give you what you need
Sorry it took so long. I have uploaded my book w/ dummy data in it. From A2, the managers name, pulls the id into e2 where I have a v or h lookup for multiple different uses. I want the E2 id to populate the e10:t10 with the values from e2:e31 based on the values from sheet 2 g2:31. I hope that makes better sense than I did last night. Thanks again for all the help!
OK thats better
Put this ARRAY formula in E10 and copy across...
=IFERROR(INDEX(Sheet2!$F$2:$F$31,SMALL(IF(Sheet2!$G$2:$G$31=$E$2,ROW(Sheet2!$G$2:$G$31)-1),COLUMNS(Sheet2!$A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
That's freaking awesome! Thank you so much! Really, bad apples man, thank you.
Is is possible to break it down a bit so I understand how to edit it to fit the real data?
Thanks again!!!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Book6.xlsx
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks