CODEWITHSUNDEEP
excelvbavlookup
user3596788
user3596788

Reputation: 95

Excel VBA Vlookup with Ranges

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

screenshot

Upvotes: 0

Views: 2659

Answers (1)

chillin
chillin

Reputation: 4486

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.

Upvotes: 3

Related Questions