I have a below formula which is giving error as "Cannot convert the value of type text to true/false", not sure what is going wrong, below is my formula and attached is my working sheet, Location filter is my output and SQIM Location normalized is my input. there is no option for attaching file 😞
Location Filter = IF ([SQIM Location normalized] = "OFS-DSC Jebel Ali" , "Dubai",
if([SQIM Location normalized] = "OFS-DSC Singapore", "Sing",
IF( [SQIM Location normalized] = "SC-DSC Logistics Houston" , "Hous",
IF ([SQIM Location normalized] = "Rott", "Rott",
IF ([SQIM Location normalized] = "KL Financial Hub Treasury" , "KLFH Treasury",
IF ([SQIM Location normalized] = "KL Financial Hub Tax" , "KLFH Tax",
IF ([SQIM Location normalized] = "KL Financial Hub General Acct" , "KLFH GA",
IF ([SQIM Location normalized] = "KL Financial Hub Shared" , "KLFH Shared",
IF ([SQIM Location normalized] = "GA" && [SQIM_Source]="KLFH Quest" , "KLFH GA",
if ([SQIM Location normalized] = "Tax" && [SQIM_Source]= "KLFH Quest" , "KLFH Tax",
if ([SQIM Location normalized] = "Treasury" && [SQIM_Source]= "KLFH Quest" , "KLFH Treasury",
IF ([SQIM Location normalized]= "OFS MEA HQ (Shared Folder)" , "KLFH Shared",
if ([SQIM Location normalized] = "GA" && [SQIM_Source]= "HFH Quest" , "HF GA",
if ([SQIM Location normalized] = "Tax" && [SQIM_Source]= "HFH Quest" , "HF Tax",
if ([SQIM Location normalized] = "Treasury" && [SQIM_Source]= "HFH Quest" , "HF Treasury",
if ([SQIM Location normalized] = "TAX" && [SQIM_Source]= "KLFH Quest" , "KLFH TAX",
if ([SQIM Location normalized] = "TAX" && [SQIM_Source]= "HFH Quest" , "HF TAX",
if ([SQIM Location normalized] = "SDM" && [SQIM_Source]= "HFH Quest" , "HF SDM",
if ([SQIM Location normalized] = "General" && [SQIM_Source] = "KLFH Quest" , "KLFH General",
if ([SQIM Location normalized] = "HFH" && [SQIM_Source]= "HFH Quest" , "KLFH General",
if ([SQIM Location normalized] = "Hub" && [SQIM_Function]= "Procurement" , "PnS Hub",
if ([SQIM Location normalized] = "HFH" && [SQIM_Source]= "HFH Quest" , "HFH Hub",
if ([SQIM Location normalized] = "KLFH GA" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH SDM" , [SQIM Location normalized],
if ([SQIM Location normalized] = "KLFH TAX" , [SQIM Location normalized],
IF( [SQIM Location normalized] ="KLFH Treasury" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KLFH Hub" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH Treasury" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH Hub" , [SQIM Location normalized],
if ([SQIM Location normalized] ="HFH GA" , [SQIM Location normalized],
if ([SQIM Location normalized] ="HFH TAX" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KLFH Accenture" ,[SQIM Location normalized],
if ([SQIM Location normalized] ="HFH Accenture" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KL Finance Hub General Acct" , "KLFH GA",
if ([SQIM Location normalized] ="KL Finance Hub Treasury","KLFH Treasury",
if ([SQIM Location normalized] ="KL Financel Hub Tax" , "KLFH TAX",
if ([SQIM Location normalized] = "PnS Hub" , "PnS Hub",
if ([SQIM_Area]= "NAR" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
if ([SQIM Location normalized]= "KL Finance Hub Tax" , "KLFH Tax",
if ([SQIM Location normalized]= "KLFH Shared" , "KLFH Shared",
if ([Geomarket (Code)]= "NGM" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
if ([Geomarket (Code)]= "NGM" && [SQIM_Area]= "NAR" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
IF( TRIM([SQIM Location normalized]) = "Houston Financial Hub Accenture (Mode 3)" && TRIM([SQIM_Source]) = "HFH Accenture" , "HFH Accenture",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Accenture (Mode 3)" && TRIM([SQIM_Source]) = "KLFH Accenture" , "KLFH Accenture",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub General Acct" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH GA",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Shared" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH Shared",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Tax" && TRIM([SQIM_Source]) = "KLFH Quest" && "KLFH Tax",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Treasury" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH Treasury",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub ERM" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH ERM",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub FA\Inventory" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH FA\Inventory",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub General Acct" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH GA",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub Tax" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH TAX",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub Treasury" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH Treasury",
IF (TRIM([SQIM Location normalized]) = "Houston Financial Hub" && TRIM([SQIM_Source]) = "HFH Quest" , "HFH Hub",
IF ([SQIM Location normalized] = "Houston Financial Hub","HFH Hub",
if ([SQIM Location normalized] = " Houston Financial Hub ", "HFH Hub", "Field")
)))))))))))))))))))))))))))))))))))))))))))))))))))))))