Linking a persons name with their staff number, VLOOKUP only gives the last name in table array.
Our take
Currently trying to automate part of my aircraft maintenance logbook, which needs me to fill out a form, and get it signed by the relevant engineer.
I have an excel sheet of all of my work experience, including the jobs I have done, which has been downloaded from the employers website. However, for the engineer field, it only gives a staff number, with the format S111111. On a second sheet, I have a list of the engineers and their staff numbers, however, they are only a numerical format, 111111.
The work experience spreadsheet is a downloaded version from our online system, but with an extra column added to the right so that the engineers name can be auto populated as and when new jobs are added to the spreadsheet.
The table with the engineers name and staff number is from another excel file, sorted lowest to higher by staff number, and then copy and pasted to the engineer list sheet on the main excel file.
I have tried a VLOOKUP, however it will only return the final name in the list. The exact formula I have used is below.
=VLOOKUP(H25,'Engineers List'!$B$3:$G$70,3,TRUE)
The formula is going in to cell BC2, H25 is the cell with the engineers staff number, 'engineer list' is the sheet with the list of engineers staff numbers (column 2) and names (column 3). True returns the final name in the list, Mr Z for example, but if I change TRUE to FALSE, I only get #N/A as a result.
I do not have access to Office 65 on my PC, so XLOOKUP is not available to me. Cells are both formatted as general. I Have tried to remove the S from the staff number and formatting both as numbers, but it returned N/A. I also tried the other way and added an S to the staff number list, but still got N/A.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Can excel find data in a sheet, if that data sometimes may not exist/be in the same cells?I am making a sheet at my work that basically sums up stats about staff each week. However, the internal source I pull the raw data from only shows employees who actually worked on a given day. My final sheet will show daily data and weekly stats. I am trying to find a way to create a formula that will match that employee's name with their data on the raw data sheet and pull it into the finished sheet. But it will not be in the same cell each time. I am trying to avoid hand-tranferring the data if possible. What I really want is a formula that will tell it "if you see Jane Doe, put the data two columns to the right of her name into this cell." Excel LTSC MSO Version 2408 English language, Windows desktop, beginner proficiency submitted by /u/pinkysaurusrawr [link] [comments]
- Output spilling over and I can't seem to fix itI'm having a problem with a spreadsheet: In A2:A200 I've got a list of employee names In C1:H1 I've got a list of departments (Finance, HR, Payroll, Maintenance, Grounds, Housekeeping, etc.) with employees parsed out in their respective columns ('John Smith' in C2 under 'Finance', for example. I'm trying to get a formula to reference values in the A column vs. where they're listed in the C through H columns and then return the department in the respective B cell (IE "John Smith" in A2 will return "Finance" in B2) I've tried =IFS(A2=C$2:C$200,"Finance",A2=D$2:D$200,"HR",A2=E$2:E$200,"Payroll") and so on with the remaining logical tests for the other departments but my output keeps spilling into A3 and down, which prevents me from using it in other cells. I can brute force my way through by putting in each employee and their department manually but I'd rather automate the process to speed it up next time I have to run this report. Any help is appreciated. Edit: I'm using Office 365 Enterprise https://preview.redd.it/3aek79h1aijg1.png?width=1722&format=png&auto=webp&s=7ee16a0540a3709d14583ce39cbb241a2dc4e63a submitted by /u/the_idea_pig [link] [comments]
- Vlookup returning no resultsI've been working in Excel and writing vlookups since Excel was Symphony 123. Whenever vlookups don't work, I'm baffled for the longest time but can usually detect my error. Not this time. The first picture shows the column I'm trying to pull from (column E). The second shows the column I'm trying to pull column E row 267 into (column O). https://preview.redd.it/60eh6pojt4zg1.jpg?width=863&format=pjpg&auto=webp&s=46e9368f3540bc3f6cf2ef646b2bdd9bd3d53bae https://preview.redd.it/0n5p9ynjt4zg1.jpg?width=1221&format=pjpg&auto=webp&s=5a639d48c38f30b38709b34ce3e722bd22832510 My formula in column N (which works) is the same as it is in column O (which doesn't work--the result should be 6) except I'm referencing column E instead of column D (5 columns over from A instead of 4). So the formula in column N is =VLOOKUP($K266,$A:$J,4,FALSE)*$M266 and the formula in column O is =VLOOKUP($K266,$A:$J,5,FALSE)*$M266. What am I missing?? All the columns are formatted identically to one another. (Yes, I did notice in my screen shots that only one decimal place is showing in column E and two in column O. I corrected that and still no 6 as a result.) submitted by /u/Ideasplease33 [link] [comments]
- Using dropbox selection to populate a cell from a separate sheet within a workbook.Hey all, sorry to ask such a stupid question, but I cannot get the right thing to happen no matter what I try. Essentially, I want to select a name from a drop down list and have the merged cells below it return an address from a separate sheet in the same workbook. I have tried some different VLOOKUP functions, but I just cannot get them to work for the life of me. I have attached a link to a PDF with some photos of my spreadsheet (I created some templates so I could keep my actual clients anonymous), but the cells in the templates would be the cells in reference on my actual sheets. Any assistance would be greatly appreciated. Edit: Since I cannot post anything in the main post with a link or photo, nor can I start with asking for help, I hope attempt number three works, and then I will try and respond with a link to a PDF of the spreadsheet in question. Edit: I figured it out. Thanks everybody, sorry to waste your time because I am an idiot. submitted by /u/ClandestineGhost [link] [comments]