Excel VBA Vlookup with Ranges

Question

I have vlookup formula that takes the value of A2 and returns the corresponding match found in my Lookup_Table in cell O2, this lookup continues for the rows underneath.

How would I modify this code to lookup a range of values in A:M, with the results placed in O:AA? Or do I have to manually code each column separately?

  With Sheets("Example")
    .Range("O2:O" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = _
        "=IF(ISERROR(VLOOKUP(A2,'Lookup_Table'!A:H,4,FALSE)),0,VLOOKUP(A2,'Lookup_Table'!A:H,4,FALSE))"
           .Range("O2:O" & .Range("A" & Rows.Count).End(xlUp).Row).Value = _
        .Range("O2:O" & .Range("A" & Rows.Count).End(xlUp).Row).Value 'Comment out if you'd like to leave the above formula in place
End With

Can you explain it a little bit better please - perhaps with screenshots? I find it rather confusing. You are populating range E2:AX and taking value from A2? Also, your formula =IF(ISERROR(VLOOKUP(B2,Lookup_Table!B:I,4,FALSE)),0,VLOOKUP(B2,Lookup_Table!B:I,4,FALSE)) is redundant, you should be using something like =IFERROR(VLOOKUP(A2,Lookup_Table!A:H,4,FALSE),0) — Michal Rosa
I appreciate your response I added a screenshot, hopefully it's less confusing — user3596788

chillin chillin · Accepted Answer · 2018-11-14T03:27:06

Assuming Lookup_Table = 'Lookup_Table'!A:H, you could try something like:

With worksheets("Cross_Walk") ' Assumes Activeworkbook
    .Range("E2:H" & .Range("A" & .Rows.Count).End(xlUp).Row).Formula = _
        "=Iferror(VLOOKUP(A2,'Lookup_Table'!$A:$H,4,FALSE),0)"
    End With

We assign the formula to range E2:H? where ? is whatever the last row is determined to be.
Excel observes relative and absolute references when assigning the same formula to a range of cells.
So since A2 in the VLOOKUP has a relative row and column reference (no $ signs), it will change to B2 when the formula is entered in F2 -- and so forth for the remaining columns and rows.
Also, if you're going to test whether the result of the VLOOKUP is an error, and then conditionally assign either zero or the matching value, you may as well just use IFERROR in your formula -- rather than performing the VLOOKUP twice.

Excel VBA Vlookup with Ranges

1 Answers