Lookup value from another workbook with multiple criteria (vba)

[ybr_15]

Hi everyone, my head is already dizzy looking for a solution. I have 2 workbooks (Salary and Result). Next, I want to get employee salaries and bonuses in the Result workbook (sheet: Data) based on the data in the Salary Workbook (sheet: List). I use 2 criteria from: 'Employee ID' and 'Employee Name' to get the desired data. Can I help do it with vba? Thank You

salary.xlsx
	A	B	C	D	E	F
1	Employee List
2
3	Employee ID	Employee Name	Gender	Department	Salary	Bonus
4	A21.2009	Williams Mary	Male	SCM	$ 35,000	$ 1,272
5	A36.2020	Brown Robert	Male	HR-GA	$ 32,000	$ 1,746
6	A75.2012	Wilson Elizabeth	Female	SCM	$ 12,000	$ 1,453
7	A47.2003	Moore Jennifer	Female	MAINTENANCE	$ 41,000	$ 1,760
8	A66.2007	Brown Charles	Male	WAREHOUSE	$ 39,000	$ 1,753
9	A44.2015	Price Lisa	Female	SCM	$ 14,000	$ 667
10	A32.2006	Wood Daniel	Male	FINANCE	$ 13,750	$ 1,413
11	A26.2016	Coleman Donald	Male	WAREHOUSE	$ 37,500	$ 610
12	A62.2008	Perry George	Female	TAX	$ 12,050	$ 1,269
13	A46.2018	Steele Donna	Female	TAX	$ 36,750	$ 1,846
14	A85.2007	Schultz Carol	Male	SCM	$ 38,050	$ 817
15	A40.2021	Munoz Ruth	Male	WAREHOUSE	$ 11,000	$ 944
16	A42.2020	Chandler Jason	Male	HR-GA	$ 29,000	$ 1,320
17	A86.2007	Small Matthew	Male	QC	$ 45,500	$ 1,846
18	A35.2016	Hensley Jessica	Female	WAREHOUSE	$ 52,000	$ 1,061
19	A91.2021	Brown Gary	Male	HR-GA	$ 8,000	$ 543
20	A78.2010	Grimes Jose	Male	HR-GA	$ 17,000	$ 1,804
21	A53.2010	Baxter Brenda	Female	SCM	$ 36,000	$ 1,141
22	A39.2018	Morin Frank	Male	TAX	$ 36,500	$ 1,471
23	A13.2000	Tillman Kathleen	Female	PRODUCTION	$ 9,750	$ 1,776
24	A92.2015	Huber Joshua	Male	QC	$ 31,750	$ 1,633
25	A94.2013	Boyle Debra	Female	PRODUCTION	$ 38,050	$ 1,560
26	A95.2005	Buckner Jerry	Male	HR-GA	$ 37,500	$ 711
27	A76.2015	Knowles Aaron	Male	PRODUCTION	$ 10,050	$ 1,193
28	A57.2002	Velazquez Carlos	Male	QC	$ 9,075	$ 1,848
29	A68.2009	Vang Marilyn	Female	PRODUCTION	$ 29,750	$ 865
List

Result.xlsx
	A	B	C	D
1	Lookup Data
2
3	Employee ID	Employee Name	Salary	Bonus
4	A75.2012	Wilson Elizabeth
5	A26.2016	Coleman Donald
6	A86.2007	Small Matthew
7	A68.2009	Vang Marilyn
8	A76.2015	Knowles Aaron
9	A13.2000	Tillman Kathleen
Data

 

[mumps]

Make sure that both workbooks are open. Place this macro in the Salary workbook and run it from there.

Sub MatchData()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook
    Dim desWS As Worksheet, srcWS As Worksheet, arr1 As Variant, arr2 As Variant, Val As String, dic As Object
    Set srcWB = ThisWorkbook.Sheets("List")
    Set desWB = Workbooks("Result.xlsx").Sheets("Data")
    Set srcWS = srcWB.Sheets("List")
    Set desWS = desWB.Sheets("Data")
    arr1 = desWS.Range("A4", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    arr2 = srcWS.Range("A4", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr1, 1)
        Val = arr1(i, 1) & "|" & arr1(i, 2)
        If Not dic.Exists(Val) Then
            dic.Add Key:=Val, Item:=i + 3
        End If
    Next i
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1) & "|" & arr2(i, 2)
        If dic.Exists(Val) Then
            desWS.Range("C" & dic(Val)).Resize(, 2).Value = Array(arr2(i, 5), arr2(i, 6))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[ybr_15]

Make sure that both workbooks are open. Place this macro in the Salary workbook and run it from there.

Sub MatchData()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook
    Dim desWS As Worksheet, srcWS As Worksheet, arr1 As Variant, arr2 As Variant, Val As String, dic As Object
    Set srcWB = ThisWorkbook.Sheets("List")
    Set desWB = Workbooks("Result.xlsx").Sheets("Data")
    Set srcWS = srcWB.Sheets("List")
    Set desWS = desWB.Sheets("Data")
    arr1 = desWS.Range("A4", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    arr2 = srcWS.Range("A4", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr1, 1)
        Val = arr1(i, 1) & "|" & arr1(i, 2)
        If Not dic.Exists(Val) Then
            dic.Add Key:=Val, Item:=i + 3
        End If
    Next i
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1) & "|" & arr2(i, 2)
        If dic.Exists(Val) Then
            desWS.Range("C" & dic(Val)).Resize(, 2).Value = Array(arr2(i, 5), arr2(i, 6))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Hi, mumps
Thanks for your answer, I made a few changes because there is still an error from your code. Your code works properly.... By the way, can you explain to me how this code works? I'm really still not familiar with Ubound. Thank You

Sub MatchData()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook
    Dim desWS As Worksheet, srcWS As Worksheet, arr1 As Variant, arr2 As Variant, Val As String, dic As Object
    Dim i As Integer
    Set srcWB = ThisWorkbook
    Set desWB = Workbooks("Result.xlsx")
    Set srcWS = srcWB.Sheets("List")
    Set desWS = desWB.Sheets("Data")
    arr1 = desWS.Range("A4", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    arr2 = srcWS.Range("A4", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr1, 1)
        Val = arr1(i, 1) & "|" & arr1(i, 2)
        If Not dic.Exists(Val) Then
            dic.Add Key:=Val, Item:=i + 3
        End If
    Next i
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1) & "|" & arr2(i, 2)
        If dic.Exists(Val) Then
            desWS.Range("C" & dic(Val)).Resize(, 2).Value = Array(arr2(i, 5), arr2(i, 6))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[ybr_15]

Regarding the previous question, we are still using the same criteria ('Employee ID' and 'Employee Name') but if the criteria are not contiguous then how to modify the code? Here is an example. Thank You

Result - Mode 2.xlsm
	B	C	D	E	F
1	Lookup Data
2
3	Employee ID	Year of Entry	Employee Name	Salary	Bonus
4	A75.2012	2012	Wilson Elizabeth
5	A26.2016	2016	Coleman Donald
6	A86.2007	2007	Small Matthew
7	A68.2009	2009	Vang Marilyn
8	A76.2015	2015	Knowles Aaron
9	A13.2000	2000	Tillman Kathleen
Data

 

[mumps]

Try:

Sub MatchData()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook
    Dim desWS As Worksheet, srcWS As Worksheet, arr1 As Variant, arr2 As Variant, Val As String, dic As Object, i As Integer
    Set srcWB = ThisWorkbook
    Set desWB = Workbooks("Result.xlsx")
    Set srcWS = srcWB.Sheets("List")
    Set desWS = desWB.Sheets("Data")
    arr1 = desWS.Range("B4", desWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 3).Value
    arr2 = srcWS.Range("A4", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr1, 1)
        Val = arr1(i, 1) & "|" & arr1(i, 3)
        If Not dic.Exists(Val) Then
            dic.Add Key:=Val, Item:=i + 3
        End If
    Next i
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1) & "|" & arr2(i, 2)
        If dic.Exists(Val) Then
            desWS.Range("E" & dic(Val)).Resize(, 2).Value = Array(arr2(i, 5), arr2(i, 6))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

In the lines of code in red, the two array variables arr1 and arr2 contain the values of the ranges in memory. This makes the code run much faster because the macro accesses the data in memory without having to go back to the sheet to access the data. The lines of code in blue simply loop through each row of each range where "UBound" represents the count of the rows in each range so based on the data you posted, UBound(aar1,1) would be equal to 26 and UBound(aar2,1) would be equal to 6. I would suggest that you do a little research using the terms "arrays in excel vba" and "scripting dictionary excel vba". These two links may be able to get you started:

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

Excel VBA Array - The Complete Guide - Excel Macro Mastery

This post provides everything you need to know about the Excel VBA Array. Includes a quickVBA Array reference guide and 50+ VBA Array examples.

excelmacromastery.com

 

[ybr_15]

Thank you, mumps. You're really helped

 

[mumps]

You are very welcome.