Help with Vlookup

[huffderi]

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"

[#2]

@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!

[#3]

@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. 

[#4]

@huffderi

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.

[#5]

@huffderi Added a sheet with the "Areas" lookup table and created a new formula. See if this solved your problem.

[#6]

@huffderi 

@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.