Help with IF formula

[Gseft]

I am using the following formula to but am having a problem if the value is equal to the value in the first line of the formula no problem. If it is the second value 17 lines down I get a FALSE. I am sure it is something obvious but not certain as to how to get this work. Looking for suggestions and ideas. Thanks

=IF(BJ2="1-02-0001-001",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Air","Air",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Ocean","Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Air_Ocean","Air_Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Land_Transport","Land_Transport",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Logistics_SCM","Logistics_SCM",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Customs","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Finance_Controlling","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="General_Management","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Human_Resources","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="ICT","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Legal_Affairs","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Marketing_PR","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Procurement","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Quality_Processes","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Sales","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Specials","Unassigned",
IF(BJ2="1-02-0001-003",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Air","Air",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Air_Ocean","Air_Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Ocean","Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Land_Transport","Land_Transport",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Logistics_SCM","Logistics_SCM",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Customs","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Finance_Controlling","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="General_Management","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Human_Resources","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="ICT","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Legal_Affairs","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Marketing_PR","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Procurement","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Quality_Processes","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Sales","Unassigned",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Specials","Unassigned",
"Unassigned"))))))))))))))))))))))))))))))))))New User Workbook_2015.zip

[mcmahobt]

I admire you on your diligence to put together such a ridiculous IF formula, but this is a nightmare. This can definitely be simplified. Can you provide a sample workbook?

[Gseft]

Had to zip up the file but it is now here.

[mcmahobt]

So i was able to open the file, but where is your current formula being applied?

[Gseft]

Yes, Sorry about that. It is in Column Y of the LMS Upload spreadsheet. There is also a similar version in Column Z. Also in the TMPUpload spreadsheet in columns Y & Z.

[Special-K]

UPDATE: Looks like my syntax is wrong, I should probably have more "Unassigned"s but you should be able to base something on this.

Can't this simply be shortened to:

=IF(BJ2="1-02-0001-001",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Air","Air",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Ocean","Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Air_Ocean","Air_Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Land_Transport","Land_Transport",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:D,3,FALSE)="Logistics_SCM","Logistics_SCM",
IF(BJ2="1-02-0001-003",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Air","Air",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Air_Ocean","Air_Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Ocean","Ocean",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Land_Transport","Land_Transport",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Logistics_SCM","Logistics_SCM",
IF(VLOOKUP(Q2,'Job Code w Training Info'!B:J,9,FALSE)="Customs","Unassigned")))))))))))))

since only the first 5 IFs in each BJ2 IF provide any data otherwise the result is "Unassigned"

FYI: Once you start creating more than about 6 compound IFs you should really be looking at creating a table and using VLOOKUP

[Gseft]

Thanks, that was how my original formula was written but was still having the same problem. I would get the desired result as long as the value=1-02-0001-001 but would receive a FALSE if value was 1-02-0001-003.

[mcmahobt]

This formula should do the same thing that your current nested IF formula is doing (pasted into Y2 of LMS Upload):

Please Login or Register  to view this content.

The problem seems to be that column O of your Hires sheet is currently not pulling data correctly from a source on your network drive.

[Gseft]

Thanks I will give that a try, and check out what is going on in the Hires sheet. Thanks again for the guidance.