Dec 01 2020 06:48 AM
I am trying to add another formula to the line but I kept getting too much argument.
This is the formula that I was given :
=IFNA(IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=27,"ICQA",IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=2,"Receive",IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=3,"Stow",IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=13,"Pick",IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=16,"AFE Pack", IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=14,"AFE Sort",IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=18,"Singles",IF(VLOOKUP(B10,Roster!$B:$I,8,FALSE)=21,"Ship Dock")))))))),"")
I need to add another Vlookup of the value of 26,"Support"
or if possible: other than these value (27,2,3,13,16,14,18,21) are there, it goes under "Support"
Dec 01 2020 07:26 AM
@huffderi Always difficult to diagnose such a monster formula with the file on hand. But why not create a separate lookup table with the values you want to check. Example attached!
Dec 01 2020 07:33 AM - edited Dec 01 2020 07:46 AM
@Riny_van_Eekelen Thank you for the suggestion. The file I am creating is a Tracker. So the "value" is basically a management code.
So for example, when I scan an employee badge, it will sync up to the roster and have their login, department, manager's name and time their badge was scanned. I have all the the department besides support.
So under the department, I don't want it to say false, I would like it to say Support.
I uploaded a sample of the tracker below.
Dec 01 2020 07:42 AM
I've notified the moderator of this site that your file needs to be taken down. You can do it yourself, and should (if you see this) before the official does. The reason: You've included a file that has names of real people. This violates the techcommunity rules, but also just isn't good practice. You wouldn't leave a printed roster of employees lying around on a public park bench; consider this the equivalent of that.
It's fine to post an example file, but always ALWAYS make any names those of Disney or Star Wars characters.
Dec 01 2020 07:43 AM - edited Dec 01 2020 07:53 AM
@huffderi Added a sheet with the "Areas" lookup table and created a new formula. See if this solved your problem.
Dec 01 2020 08:17 AM
@Riny_van_Eekelen is exactly correct with his suggestion. That formula you were given to work with is an example of a terrible practice known as "hard coding" of values. You've (or your predecessor who created it) have hard coded into the nested series of IF(VLOOKUP.... functions a series of answers, one-by-one, to the managerial codes for the areas in which these employees work. So as a new area gets added, you've got to go in and add yet another nested value. Or if, perish the thought, the place gets reorganized and names of areas changed, you've got to re-write the whole formula.
Hard-coding of values into functions is to be avoided.
And a separate table, such as @Riny_van_Eekelen has suggested, is the solution.
Here, for example, is what such a table might look like.
Dept Codes | Dept Names |
2 | Receive |
3 | Stow |
5 | Dpt5 |
13 | Pick |
16 | AFE Pack |
18 | Singles |
19 | Dpt19 |
21 | Ship Dock |
24 | Dpt24 |
26 | Support |
27 | ICQA |
43 | Dpt43 |
0 | Missing |
You then can do a simple VLOOKUP nested within another VLOOKUP. The inner VLOOKUP first finds the management area code associated with the employee, and the outer VLOOKUP, using that code, searches in this separate table for the name of that management area. When names get changed, you change the table, not the formula.