Help with Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-1944640%22%20slang%3D%22en-US%22%3EHelp%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1944640%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20add%20another%20formula%20to%20the%20line%20but%20I%20kept%20getting%20too%20much%20argument.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20that%20I%20was%20given%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFNA(IF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D27%2C%22ICQA%22%2CIF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D2%2C%22Receive%22%2CIF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D3%2C%22Stow%22%2CIF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D13%2C%22Pick%22%2CIF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D16%2C%22AFE%20Pack%22%2C%20IF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D14%2C%22AFE%20Sort%22%2CIF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D18%2C%22Singles%22%2CIF(VLOOKUP(B10%2CRoster!%24B%3A%24I%2C8%2CFALSE)%3D21%2C%22Ship%20Dock%22))))))))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20add%20another%20Vlookup%20of%20the%20value%20of%2026%2C%22Support%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20if%20possible%3A%20other%20than%20these%20value%20(27%2C2%2C3%2C13%2C16%2C14%2C18%2C21)%20are%20there%2C%20it%20goes%20under%20%22Support%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1944640%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1944785%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1944785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F887151%22%20target%3D%22_blank%22%3E%40huffderi%3C%2FA%3E%26nbsp%3BAlways%20difficult%20to%20diagnose%20such%20a%20monster%20formula%20with%20the%20file%20on%20hand.%20But%20why%20not%20create%20a%20separate%20lookup%20table%20with%20the%20values%20you%20want%20to%20check.%20Example%20attached!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-01%20at%2016.25.44.png%22%20style%3D%22width%3A%20373px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236913i79406203E976E636%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-01%20at%2016.25.44.png%22%20alt%3D%22Screenshot%202020-12-01%20at%2016.25.44.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1944830%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1944830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20suggestion.%20The%20file%20I%20am%20creating%20is%20a%20Tracker.%20So%20the%20%22value%22%20is%20basically%20a%20management%20code.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20example%2C%20when%20I%20scan%20an%20employee%20badge%2C%20it%20will%20sync%20up%20to%20the%20roster%20and%20have%20their%20login%2C%20department%2C%20manager's%20name%20and%20time%20their%20badge%20was%20scanned.%20I%20have%20all%20the%20the%20department%20besides%20support.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20under%20the%20department%2C%20I%20don't%20want%20it%20to%20say%20false%2C%20I%20would%20like%20it%20to%20say%20Support.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20uploaded%20a%20sample%20of%20the%20tracker%20below.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1944861%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1944861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F887151%22%20target%3D%22_blank%22%3E%40huffderi%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20notified%20the%20moderator%20of%20this%20site%20that%20%3CEM%3Eyour%20file%20needs%20to%20be%20taken%20down.%3C%2FEM%3E%20You%20can%20do%20it%20yourself%2C%20and%20should%20(if%20you%20see%20this)%20before%20the%20official%20does.%20The%20reason%3A%20%3CU%3E%3CSTRONG%3EYou've%20included%20a%20file%20that%20has%20names%20of%20real%20people%3C%2FSTRONG%3E%3C%2FU%3E.%20This%20violates%20the%20techcommunity%20rules%2C%20but%20also%20just%20isn't%20good%20practice.%26nbsp%3B%20You%20wouldn't%20leave%20a%20printed%20roster%20of%20employees%20lying%20around%20on%20a%20public%20park%20bench%3B%20consider%20this%20the%20equivalent%20of%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20fine%20to%20post%20an%20example%20file%2C%20but%20always%20ALWAYS%20make%20any%20names%20those%20of%20Disney%20or%20Star%20Wars%20characters.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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"

5 Replies

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

Screenshot 2020-12-01 at 16.25.44.png

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

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

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

 

@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 CodesDept Names
2Receive
3Stow
5Dpt5
13Pick
16AFE Pack
18Singles
19Dpt19
21Ship Dock
24Dpt24
26Support
27ICQA
43Dpt43
0Missing

 

 

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.