Array lookups

[margr]

Hello everyone,

I tried to do a index-match with arrays and could return results one by one. I am trying to return all results at once and I'm having difficulties In bdData keys are in column R and other 5 columns contain values to be returne; on ws Data, the range on col B is a list of all unique keys from column R bdData. Purpose is to fill ws Data ranges of cols D, E, F, H, I with respective info from bdData. Faulty code below, thanks!

    Data = ws.Range("B3:B" & lRow).Value
    bdData = ws_b.Range("N2:AB" & lRow_b).Value
    
    ReDim foundValues(1 To UBound(Data, 1), 1 To 5)
    
    For i = 1 To UBound(Data, 1)
        keyValue = Data(i, 1)
        
        For j = 1 To UBound(bdData, 1)
            If keyValue = bdData(j, 5) Then
                foundValues(i, 1) = bdData(j, 8)
                foundValues(i, 2) = bdData(j, 1)
                foundValues(i, 3) = bdData(j, 15)
                foundValues(i, 4) = bdData(j, 4)
                foundValues(i, 5) = bdData(j, 13)
                
                Exit For
            End If
        Next j
    Next i
    
With ws
    .Range("D3").Resize(UBound(foundValues, 1), 1).Value = foundValues
    .Range("E3").Resize(UBound(foundValues, 2), 1).Value = foundValues
    .Range("F3").Resize(UBound(foundValues, 3), 1).Value = foundValues
    .Range("H3").Resize(UBound(foundValues, 4), 1).Value = foundValues
    .Range("I3").Resize(UBound(foundValues, 5), 1).Value = foundValues
End With

    Set Data = Nothing
    Set bdData = Nothing
    Erase foundValues

 

[duggie33]

Hi margr,

Where do you get an error in your code? It would be easier to troubleshoot if you could supply some example data and the entire code. Please use the XL2BB share example data.

Doug

 

[Alex Blakenburg]

Is there any chance you are just looking for something like this:

    With ws
        .Range("D3").Resize(UBound(foundValues), 3).Value = Application.Index(foundValues, 0, Array(1, 2, 3))
        .Range("H3").Resize(UBound(foundValues), 2).Value = Application.Index(foundValues, 0, Array(4, 5))
    End With