Perform a Vlook Up or Find Per column

Please help me,

Take for example i have a this kind of data set

In Sheet1

Column A
614545
546425
456426

In Sheet 4, I have a named table like this:

Column A    Column B    Column C

614545      AAA         1111
564645      AXS         1254
123545      XSF         4524
(And so forth...)

Now what i need is a code that would search For the corresponding Value of Sheet 1 Column A to the table in sheet 4. As for example, the code would give this result

Sheet1

Column A    Col B   Col C
614545      AAA     CCC

(And so forth...)

I've been trying to solve this problem for weeks now. But I just couldnt fix it. I am onl able to perform a find function at one cell at a time. I need a code that would traverse to the whole column A (Sheet1) and would return the corresponding values on t开发者_如何学编程he table in sheet(4).

Please help me.

Try this - you'll need to update the data ranges as per your requirements

Sub LookUpValues()
    Dim sourceRng As Range, dataTable As Range, cl As Range

    Set sourceRng = Worksheets("Sheet1").Range("A1:A10") 
    Set dataTable = Worksheets("Sheet4").Range("A1:C100") 

    For Each cl In sourceRng
        cl.Offset(0, 1) = WorksheetFunction.VLookup(cl, dataTable, 2, False)
        cl.Offset(0, 2) = WorksheetFunction.VLookup(cl, dataTable, 3, False)
    Next cl
End Sub

Just to add 'VLookup' is perfectly fine in most cases but if your Excel data starts to become large then 'VLookup' is notoriously slow. The .Find method is much quicker.

This example UDF function - use formula in Excel =AltLookup(cell,column) Eg =AltLookup(A1,3) will lookup the text in cell A1 on Sheet1 in column A:A of Sheet4 and return column 3 or column C

Function AltLookup(ByVal strToFind As String, ByVal column As Long)
Dim rngFind As Range

With Sheet4 'change sheet to lookup on

    With .Columns("A:A") 'assuming lookup on column A

    Set rngFind = .Find(What:=strToFind, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Not rngFind Is Nothing Then
        AltLookup = .Cells(rngFind.Row, column).Value
    Else
        'do nothing or.. AltLookup = vbnullstring
    End If

    End With
End With
End Function