Compare and Replace Formula

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,500
Members
417,148
Latest member
pe3087te

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top