Match values in a range with another range and copy the adjacent column

**xelmac** · 08-13-2012, 09:20 PM

Hello All,
I am learning to write macros in excel and would appreciate your help. I have 4 values in column A1 through A4. I have another set of data in Column B1 through B100. I would like to check if the values in column A exist in column B and if it does then I would like the value in the adjacent cell (column C) to be copied in any other cell. I started structuring a program but I am not able to get what I want.

Thanks.

**sunflowers** · 08-13-2012, 09:35 PM

Try this :

PHP Code:


Option Explicit

Sub Check()
Dim i As Long, j As Long, k As Long
Dim ConD(), Arr(), dArr()
With Sheet1
ConD = .Range("A1:A" & .[A65536].End(xlUp).Row).Value
Arr = .Range("B1:B" & .[B65536].End(xlUp).Row).Value
ReDim dArr(1 To UBound(Arr), 1 To 1)

For j = 1 To UBound(ConD, 1)
    For i = 1 To UBound(Arr, 1)
        If Arr(i, 1) = ConD(j, 1) Then
            k = k + 1
            dArr(k, 1) = Arr(i, 1)
        End If
    Next
Next
.[C1].Resize(k, 1) = dArr
End With
End Sub

Thanks!!!!!

**jindon** · 08-13-2012, 10:58 PM

If my understanding is correct

=IFERROR(VLOOKUP(A1,$B$1:$C$10,2,FALSE),"")

then copy down.

**xelmac** · 08-15-2012, 01:42 PM

Thanks Jindon and Sunflowers. I ended up relpacing Vlookup with index match because I had several columns from which I needed the matching data and it worked. I will go over the macro now.

Regards.