CODEWITHSUNDEEP
excelexcel-formula
Yuddy
Yuddy

Reputation: 49

How to find and replace multiple values in Excel at the same time?

I have a very large Excel sheet with 1000's of values that contain over 200 different countries, I want to change each country to either Zone 1, Zone 2, Zone 3 ... to Zone 10 etc. I also want to be able to enter Countries in the future, into this sheet, and have them automatically change to the zone that I have specified.

For example, 'Jersey' would become 'Zone 1' and 'Monaco' would become 'Zone 1' also, whereas 'Norway' would become 'Zone 3'.

I have a large table with the Countries and the Zones to the specific country: Big list with over 200 counties below:

Jersey         Zone 1
Monaco         Zone 1
San Marino     Zone 1
Albania        Zone 2
Greenland      Zone 2
Norway         Zone 3
etc etc...     ...
               Zone 10

Section of list I want converted:

Jersey
Monaco
San Marino
Albania
Greenland
Norway
UK
USA
Ireland
France
Spain

My attempted code for this so far (Which does nothing) is to try and Substitute the values with other values that are in the columns above, here G3:G208 is the Countries above, and H3:H208 is the zones. I want this to affect the whole sheet, all countries must become the zone specified, and future entries will change to the specified Zone automatically.

=SUBSTITUTE(A1:F999,INDEX(G3:G208,I4:N1003),INDEX(H3:H208,,I4:N1003))

Thank you.

Upvotes: 1

Views: 341

Answers (1)

Gravitate
Gravitate

Reputation: 3064

I would strongly recommend using INDEX/MATCH, rather than a VLOOKUP. It is much quicker when dealing with a large number of formulas. Also, although your requirements are very basic, INDEX/MATCH is much more versatile, so would be useful in more complex situations.

enter image description here

=IFNA(
    INDEX($E$3:$E$10,MATCH($A3,$D$3:$D$10,0)),
    "Not Found"
)

EDIT: I miss understood what the OP wanted...

Sorry. I think what you actually want is the original data to be overwritten. So the cell containing "Jersey", changes to contain "Zone 1" instead. I don't think this is possible without a VBA macro.

You could create a separate sheet which would act as a read only report. It would be identical to the original "input" sheet, except that it changes the cells containing countries to zones.

Upvotes: 1

Related Questions