Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I trying to compare lists in excel. There are two lists, one list just has one column and the other has two columns, and what I am trying to do is when column A matches column C than take the value in column D and use that to replace column A.

For example:

Column A  Column B Column C Column D
Blue               Blue     Shirt   
Blue               Red      Pants
Red                Green    Shoes

So the completed list should look like:

Column A  Column B Column C Column D
Shirt              Blue     Shirt   
Shirt              Red      Pants
Pants              Green    Shoes
share|improve this question

This will work until you hit an error. And then it stops working. Not sure why, maybe you or someone else could fix it, but it will work for your selection as long as your selection is found (e.g. "purple" breaks the loop)

Sub test()
Dim a As Variant
Application.ScreenUpdating = False

On Error Resume Next


For Each c In Selection

a = Application.WorksheetFunction.VLookup(c, Range("C1:D3"), 2, False)

If Err.Number = 0 Then c.Value = a


Application.ScreenUpdating = True

End Sub
share|improve this answer

In another column (e.g. B), enter the following formula and fill down:


Then copy the resulting column, and Paste Special -> Values only over column A. Then you can delete the formulas from Column B.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.