Compare and Replace Formula

[MHamid]

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:

=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:

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.

 

[mart37]

You can use the option to split text to columns. Split on ;
Then you can use Vlookup.
With CONCATENATE you can combine the different cells.