Help in lookup using vba

[yallamanda.b]

HI Team,

please help me in lookup based on first name and last name The salary will fill using vba code in dyanmic way.

i had know how to solve in using formulas in excel but need help in same as through vba.

Thanks

 

[Chihiro]

Something like this.

Run it while you have Sheet1 active.

Sub Test()
Dim i As Long

tempArray = [A1].CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(tempArray, 1)
        .Item(tempArray(i, 1) & "," & tempArray(i, 2)) = tempArray(i, 3)
    Next
    [F4].Value = Format(.Item([F2] & "," & [F3]), "$#,##0.00")
End With
End Sub

Edit: Woops. Didn't see the "L" column part. Use below instead.

Sub Test()
Dim i As Long, j As Long
Dim resArray

tempArray = [A1].CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(tempArray, 1)
        .Item(tempArray(i, 1) & "," & tempArray(i, 2)) = tempArray(i, 3)
    Next

    tempArray = [J1].CurrentRegion.Value
    ReDim resArray(UBound(tempArray, 1), 1)
    For j = 2 To UBound(tempArray, 1)
        resArray(j - 2, 0) = .Item(tempArray(j, 1) & "," & tempArray(j, 2))
    Next
End With
[L2].Resize(UBound(resArray, 1)) = resArray
End Sub

For something more versatile see link for details.
http://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/

 

[Chihiro]

But really, formula is preferable unless there are some other factors that influences it.

In L2: Confirmed as Array (CSE) and dragged down.
=INDEX($C$2:$C$8,MATCH(J2&K2,$A$2:$A$8&$B$2:$B$8,0))

 

[yallamanda.b]

Thanks chihiro...