humility36
New Member
- Joined
- Dec 16, 2019
- Messages
- 7
- Office Version
- 2016
- Platform
- Windows
Good Afternoon Excel Team,
I've been given a task to clean up an excel spreadsheet and create a better way to view the data. I've tried to use VLOOKUP and HLOOKUP but it does not seem to be working right. Let me explain a little more
I have a spreadsheet that has employee information and areas that they are trained in. I need to be able to pull up their name and then display all the areas that they are trained in.
ABOVE is the format and layout of my table. My lookup value is their unique ID number and I can do a VLOOKUP on the ID to pull their first name, last name, shift, and department. The problem is that I need to pull the column name of the areas that they are trained in. I've been fighting with Index / Match, SEARCH, VLOOKUP, and HLOOKUP functions but I cannot get it right. Here's the logic:
1. Insert ID
2. Populate first name, last name, shift, department, and then list out areas they are trained in
FUTURE STATE:
I enter the ID number in A3 and the rest of the information populates. I'm having trouble figuring out how to search for non-empty cell and then displaying the column and then also displaying the contents of the Cell
If there is any way you guys can help me with this, I would be super grateful. With over 300 names, it's very hard manage and hunt and find it all.
Thanks everyone for your help!
- Humility36
I've been given a task to clean up an excel spreadsheet and create a better way to view the data. I've tried to use VLOOKUP and HLOOKUP but it does not seem to be working right. Let me explain a little more
I have a spreadsheet that has employee information and areas that they are trained in. I need to be able to pull up their name and then display all the areas that they are trained in.
ID | First Name | Last Name | Shift | Department | Shipping | Building | Receiving | PutAways | Forklift | |
---|---|---|---|---|---|---|---|---|---|---|
1 | A | B | C | D | E | F | G | H | I | J |
2 | 23645 | George | Lucas | Nights | QA | PO | X | X | ||
3 | 55698 | Sally | Mae | Days | Shipping | X | X | X | ||
4 | 99687 | John | Doe | Nights | Building | X | X | NC | ||
5 | 89778 | Steve | Smith | Days | Receiving | X | NT | X | ||
6 | LEGEND | |||||||||
X = trained | NT = needs trained | PO = position offered | NC = Needs Certified |
ABOVE is the format and layout of my table. My lookup value is their unique ID number and I can do a VLOOKUP on the ID to pull their first name, last name, shift, and department. The problem is that I need to pull the column name of the areas that they are trained in. I've been fighting with Index / Match, SEARCH, VLOOKUP, and HLOOKUP functions but I cannot get it right. Here's the logic:
1. Insert ID
2. Populate first name, last name, shift, department, and then list out areas they are trained in
FUTURE STATE:
I enter the ID number in A3 and the rest of the information populates. I'm having trouble figuring out how to search for non-empty cell and then displaying the column and then also displaying the contents of the Cell
A | B | C | D | E | |||
1 | This is where I need help in this column. If the cell is not empty, the display the column name | If the cell is NOT empty, display the contents of the cell. | |||||
2 | ID | FIRST | LAST | Shift | Department | Areas Trained | Icon |
3 | 89778 | Steve | Smith | Days | Receiving | Receiving | X |
4 | Forklift | X | |||||
5 | Putaways | NT | |||||
6 | |||||||
7 |
If there is any way you guys can help me with this, I would be super grateful. With over 300 names, it's very hard manage and hunt and find it all.
Thanks everyone for your help!
- Humility36