Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
Red
Green
Green
Purple

So the completed list should look like:

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

2 Answers

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

Err.Clear

For Each c In Selection

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

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

Next

Application.ScreenUpdating = True

End Sub
share|improve this answer
add comment

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

=IFERROR(VLOOKUP(A1,$C$1:$D$3,2,FALSE),A1)

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
add comment

Your Answer

 
discard

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.