Hello,
I have a nested IF formula that brings back Country field from 4 different worksheets. When I do my second If vlookup instance, I need to compare the countries in the second sheet (Check_Weekly Tracker) to a completely separate sheet (Countries Code) where in Column A is a list of the Country name (as shown in Check_Weekly Tracker) and Column B is a list of the coded country. When a comparison is done between the country from (Check_Weekly Tracker) sheet and the country in the (Countries Code) sheet, I need to bring back the coded country in Column B.
This is the formula in my main sheet that I want to tweak:
This is the piece of that formula that I need to perform another comparison to bring over the coded country:
The issue is that sometimes there are multiple countries within a cell separated by a semicolon no spaces.
This is the original data in the (Check_Weekly Tracker) sheet:
This is the result I need in my main sheet:
Any help will be greatly appreciated. And again, I am sorry I could not upload a sample spreadsheet.
I have a nested IF formula that brings back Country field from 4 different worksheets. When I do my second If vlookup instance, I need to compare the countries in the second sheet (Check_Weekly Tracker) to a completely separate sheet (Countries Code) where in Column A is a list of the Country name (as shown in Check_Weekly Tracker) and Column B is a list of the coded country. When a comparison is done between the country from (Check_Weekly Tracker) sheet and the country in the (Countries Code) sheet, I need to bring back the coded country in Column B.
This is the formula in my main sheet that I want to tweak:
Excel Formula:
=IF(IF($R7<>"",VLOOKUP($K7,[2021_IA_Plan_Gold_Copy.xlsm]Check_YTD!$B:$J,9,0),IF($Q7<>"",VLOOKUP($K7,Check_WeeklyTracker!$A:$L,12,0),IF(Q7="",VLOOKUP($K7,[2021_IA_Plan_Gold_Copy.xlsm]Prelim_AuditPlan!$B:$N,13,0),VLOOKUP($K7,[2021_IA_Plan_Gold_Copy.xlsm]IER_Tracker!$F:$H,3,0))))=0,"",IF($R7<>"",VLOOKUP($K7,[2021_IA_Plan_Gold_Copy.xlsm]Check_YTD!$B:$J,9,0),IF($Q7<>"",VLOOKUP($K7,Check_WeeklyTracker!$A:$L,12,0),IF(Q7="",VLOOKUP($K7,[2021_IA_Plan_Gold_Copy.xlsm]Prelim_AuditPlan!$B:$N,13,0),VLOOKUP($K7,[2021_IA_Plan_Gold_Copy.xlsm]IER_Tracker!$F:$H,3,0)))))
This is the piece of that formula that I need to perform another comparison to bring over the coded country:
Excel Formula:
IF($Q7<>"",VLOOKUP($K7,Check_WeeklyTracker!$A:$L,12,0)
The issue is that sometimes there are multiple countries within a cell separated by a semicolon no spaces.
This is the original data in the (Check_Weekly Tracker) sheet:
Country |
US |
Switzerland;Channel Islands;UK;Jersey |
Japan;India;Philippines;Taiwan;Australia;Hong Kong/Macau;Hong Kong;Malaysia;Thailand;Singapore;Vietnam;Indonesia;China;Korea |
This is the result I need in my main sheet:
Country |
US(US) |
Switzerland(CH);Channel Islands(1152);UK(UK);Jersey(JE) |
Japan(JP);India(IN);Philippines(PH);Taiwan(TW);Australia(AU);Hong Kong(HK);Macau(MO);Hong Kong(HK);Malaysia(MY);Thailand(TH);Singapore(SG);Vietnam(VN);Indonesia(ID);China(CH);Korea(KR) |
Any help will be greatly appreciated. And again, I am sorry I could not upload a sample spreadsheet.