Forum Discussion
Solved
Vlookup help
I have done the basic Vlookup to get data from one page to another, my Excel skill is just above beginner. I created this skill matrix and I was wondering if is possible to create a page where if i enter the name and last name of the employee ( we have some siblings and relatives so both conditions have to be meet) I will display all the Employees operations where there is a number and the number so it will look like the back of a baseball card.
| operations | ||||||||
| Last | First | Role | MRP | Setup | Troubleshooting | Inspection | op1 | op2 |
| Filter | ||||||||
| name1 | lastname1 | machineoperator1 | ||||||
| name2 | lastname2 | machineoperator2 | 2 | |||||
| name3 | lastname3 | machineoperator3 | 1 | |||||
| name4 | lastname4 | machineoperator4 | ||||||
| name5 | lastname5 | machineoperator5 | 4 | |||||
| name6 | lastname6 | machineoperator6 | 1 | |||||
| name7 | lastname7 | machineoperator7 | 3 | 3 | ||||
| name8 | lastname8 | machineoperator8 | 1 | 3 | ||||
| name9 | lastname9 | machineoperator9 | 1 | |||||
| name10 | lastname10 | machineoperator10 | 2 | 3 | 3 | |||
| name11 | lastname11 | machineoperator11 | 1 | 0 | 0 | 0 | 3 | 2 |
| name12 | lastname12 | machineoperator12 | ||||||
| name13 | lastname13 | machineoperator13 | ||||||
| name14 | lastname14 | machineoperator14 | ||||||
something like this:
| name11 | lastname11 |
| Role: | machineoperator11 |
| MRP | 1 |
| Setup | 0 |
| Troubleshooting | 0 |
| Inspection | 0 |
Thank you in advance for any guidance
Hi Luchoarenas12 , the transpose function works quite well. When you use the XLOOKUP function, it will return an array and spill your data horizontally. You would then wrap the Xlookup function in the transpose function to get your final result.
First do an xlookup using the name and last name and reference your table as an array. Then return the fields you want. It will spill as a horizonal array in your sheet. Then wrap it around with the Transpose function and it will lay it out vertically.
I have attached the solution in a workbook using the names and staff number. Please let me know if this works and if it does, please mark as solved. Thank you.
I applied the =(TRANSPOSE(XLOOKUP() ) on my original spreadsheet and it work wonders, but because my list of skills is very large some employees have a long list of 0s, I can use a filter above my operator role to filter the 0s, but I was wondering if the "if no found" feature of Xlookup would allow me to filter blanks cells, because empty cells have no data on my table.
Yes, you could use the "if not found" option in XLOOKUP but most times when it returns 0, it may mean that the record was found but there was no corresponding value in the field that is to be returned.
As a work around, you could try wrapping your formula in an IF function. For example, IF(B2 = 0,"No record",TRANSPOSE(XLOOKUP()))
Not sure what you mean by "I went on a search for employee numbers" -- if you're in an organization of any size, they certainly exist. It's possible you're not in a position to be granted access to them (for privacy reasons), but if that's the case, for your purposes only you could simply assign unique numbers to each person under your purview.
Thank you! I went on the search for employees numbers to use as reference, I have no made changes to my original file, I tried the transpose advise but i think I messed the cell numbers.
Hi Luchoarenas12 , the transpose function works quite well. When you use the XLOOKUP function, it will return an array and spill your data horizontally. You would then wrap the Xlookup function in the transpose function to get your final result.
First do an xlookup using the name and last name and reference your table as an array. Then return the fields you want. It will spill as a horizonal array in your sheet. Then wrap it around with the Transpose function and it will lay it out vertically.
I have attached the solution in a workbook using the names and staff number. Please let me know if this works and if it does, please mark as solved. Thank you.
Thank you! this is what I had in mind. I really appreciate all the help
Hi Luchoarenas12, I created a video of the solution JKPieterse shared and did some with the staff number as suggested by mathetes. You can check out the video of the solution on my Tiktok (https://vm.tiktok.com/ZNdeyEyoS/) or Youtube page (https://youtube.com/shorts/qlZo3rdhnJg).
the Youtube video seems to present the video on TikTok format, it does not show the spreadsheet on a size that would make it readable. no sure if there is a setting that would allow you to display it on a landscape mode. I might download TikTok to see if is looks better on my phone.
I would suggest (writing as a former director of a major corporation's hr database), instead of just first and last names, since this IS an employee list, that you use a unique employee number--surely there is one--or if you don't have your own internal system, each person's SSN. That's a far more long-term-reliable method of identification than names. Inevitably, somebody else will come along with the same name as an already established employee.
Once you have done that, you can modify the formula from JKPieterse or develop your own more simple formula to extract the data.
Thank you! that's a great idea. I have no applied JKPieterse formula yet. but yeah the employee number will make the reference easier lookup value easier to match.
Assuming that table you posted starts in cell A1, and the top of the card is in cell A20, this formula will get the values from the list:
=TRANSPOSE(XLOOKUP(A20&"|"&B20,$A$5:$A$18&"|"&$B$5:$B$18,$C$5:$I$18))
