Excel - Replace values in one column by values in another column

Question

I have a lot fo data in Excel spreadsheet. One of the column keeps the ISO code of the country. I would like to replace this value with the whole name of the country. Of course there are lists of ISO codes and corresponding country names on the internet, but I would like to know, if there's a better way than Ctrl+H thousand times for each ISO code value.

Thank you in advance.

Example: (I would like to replace values in A by these in E based on value from D)

by reference note the $ vs without when referencing other cells. — Ronnie Smith
– Ronnie Smith, Commented Feb 18, 2017 at 23:02

b-poe · Accepted Answer · 2017-02-18 23:05:32Z

8

Make a new column adjacent to the column with ISO codes. Then you can perform an index(match()).

Using your example above the formula in B1 would look like this:

=INDEX($E$1:$E$5,MATCH($A1,$D$1:$D$5,0))

You should then be able to drag the formula down and refresh to find a list of country names appearing next to your ISO codes.

NOTE: I would keep the ISO > Country Name master table on a separate worksheet for easy maintenance and clarity.

answered Feb 18, 2017 at 23:05

b-poe

1638 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel - Replace values in one column by values in another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related