Compare columns and replace values?

**llckll** · 06-28-2010, 11:49 AM

Hello all,

I am new to this site and excel and hope you can help me in what I'm trying to achieve.

Basically, I have two worksheets.

Worksheet #1 contains a CEP and UF_ column. CEP is the zip code for Brazil cities and UF_ is the state abbreviation.

Worksheet #2 contains a CEP and UF_ column as well.

Worksheet #1 contains the correct CEP matched with the correct UF_.

What must I do to have the CEP in Worksheet #2 search and match the CEP in Worksheet #1, and if there is a match, the UF_ field in Worksheet #2 will be updated with the value of the UF_ field in Worksheet #1?

Example file attached.

Thanks in advance.

**werfnort** · 06-28-2010, 03:55 PM

Sounds like a simple VLOOKUP to me. Use the Excel Help for more information, but basically, you put the formula in Worksheet2 in the UF_ column. The lookup value would be the CEP cell in that row. The lookup table would be in Worksheet1, make sure to lock in the cell references with $s.

**llckll** · 06-28-2010, 04:02 PM

Thanks. Would you be able to show me an example in the spreadsheet I attached?

**werfnort** · 06-28-2010, 11:47 PM

Attached. I left the formula in there so you can see how it works. You can copy everything and Paste as Values when you're ready to use it. Just do a Find/Replace for #N/A to get rid of those, and you're left with only UFs that matched a CEP in the first worksheet.