locked
How to quick get value in a column from another sheet RRS feed

  • Question

  • Hello All, 

    I have two sheets, one sheet name "data", another is report sheet, 

    I want to get B column value  in " report" sheet from data sheet. 

    A column  report sheet  value  be find in A column in data sheet, then it's equal to A column ,  or find  A column  report sheet  value in B column  and bring out C value. 

    Because the line is more then thousand counts, it takes me lot of time to vlook up value from data sheet with VBA. 

    Is anyone can help me to find out  a quick way in VBA ? many thanks. 

            report sheet                                data sheet

      A column  B column                         A column        B column       C column    

       7125K      7125                                      4518          7125K             7125

       12356K    12356                                    1234          12356K          12356

        4518      4518                                      5566            9813A             9813

        8823G    8823                                        7234            8823G           8823

        7234       7234                                       ...........................................

        ......

    Saturday, October 5, 2019 10:05 AM

All replies

  • You could use the following formula in B1 on the Report sheet:

    =IFERROR(IFERROR(VLOOKUP(A1,Data!$A$1:$A$3000,1,FALSE),VLOOKUP(A1,Data!$B$1:$C$3000,2,FALSE)),"")

    and fill or copy down. If you prefer VBA:

    Sub FillData()
        Dim wsh As Worksheet
        Dim m As Long
        Set wsh = Worksheets("Report")
        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
        With wsh.Range("B1:B" & m)
            .Formula = "=IFERROR(IFERROR(VLOOKUP(A1,Data!$A$1:$A$3000,1,FALSE)," & _
                "VLOOKUP(A1,Data!$B$1:$C$3000,2,FALSE)),"""")"
            .Value = .Value
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, October 5, 2019 10:54 AM