Need to apply advanced array vlookup formula in vba code

[Pankaj jaswani]

Dear Expert,


In vba coding, I'm trying to get multiple values from an array using an advanced vlookup formula. But I'm unable to do so because I can't find it on Google or YouTube. Please assist me in assigning the formula below to the "with statement" in vba coding.

In Columns (U, V, W, X) Formula: =VLOOKUP(R2,Mapping!A:AB,{13,15,21,25},FALSE)

Sub Vlookuparray()
Dim LastRow    As Long
With Sheets("Data"): LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row: End With
    With Columns (U:X)
        .Range("U2:U" & LastRow:"X2:X" & LastRow).Value.formulaarray = "VLOOKUP(R2,Mapping!A:AB,{13,15,21,25},FALSE)"
    End With

[chocolate_kitten]

Hi Pankaj jaswani,

Please try:

1. For dynamic array

Sub Vlookuparray_Dynamic()
    Dim LastRow    As Long

    With Sheets("Data")
    
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("U2:U" & LastRow).Formula2 = "=VLOOKUP(R2,Mapping!A:AB,{13,15,21,25},FALSE)"
        
    End With
End Sub

2. For non-dynamic array

Sub Vlookuparray()
    Dim LastRow    As Long

    With Sheets("Data")
    
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("U2").Resize(1, 4).FormulaArray = "=VLOOKUP(R2,Mapping!A:AB,{13,15,21,25},FALSE)"
        .Range("U2").Resize(1, 4).Select
        Selection.AutoFill Destination:=Range("U2:X" & LastRow), Type:=xlFillDefault
        
    End With
End Sub

[Pankaj jaswani]

Thank you so much!! @chocolate_kitten