Index and Match array (VBA)

[Khoong]

Hi

I am trying to deal with thousands of rows of data for updating a montly report.

INDEX and MATCH with multiple criteria

To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. In the example shown, the formula in H8 is: =INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)) The result is $17.00, the price of a Large Red T-shirt. This is an array formula and must be...

exceljet.net

I have refer above pages to found a way to make what I need by using formula but the problem now is I am trying to apply this formula into VBA to get what I need.
For easy understading I have attached below picture.

What I am tryiing to do is that I need to index match Sheet1"A" with Sheets2"A" and Sheet1"E" with Sheet2"B" then return result Sheet1"F" = Sheet2"C" ; Sheet1"G" = Sheet2"D" and so on
Note that: below is just exmaple the real data need to update Sheet1 from F:AJ

 

[SanjayGMusafir]

What I am tryiing to do is that I need to index match Sheet1"A" with Sheets2"A" and Sheet1"E" with Sheet2"B" then return result Sheet1"F" = Sheet2"C"

In Sheet1 Cell F2 put formula -

=XLOOKUP(1,($A2=Sheet2!$A:$A)*($E2=Sheet2!$B:$B),Sheet2!C:C)

If it works the intended way you can fill it down and right in both directions.

 

[offthelip]

welcome to the forum, here is the super fast VBA way of dealing with this problem, my code uses a VBA dictionary and variant arrays which is a much faster way of doing this task than using the excel functions.

Sub dictionary()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
 
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Sheet2")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(lastrow, 4))
   End With
   For i = 2 To UBound(Ary)
    ' concatentate colums A and B
      temp = Ary(i, 1) & Ary(i, 2)
      Dic(temp) = i                 ' save array row in dictionary
   Next i
   With Worksheets("Sheet1")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      inarr = .Range(.Cells(1, 1), .Cells(lastrow, 5))
      outarr = .Range(.Cells(1, 6), .Cells(lastrow, 7))
   
      For i = 2 To lastrow
         temp = inarr(i, 1) & inarr(i, 5)  '     ' concatentate colums A and E
         indi = Dic(temp) 'this matches the value given by the index in the dictionary
         outarr(i, 1) = Ary(indi, 3) ' copy column C of sheet 2 to column F of sheet 1
         outarr(i, 2) = Ary(indi, 4) ' copy column D of sheet 2 to column G of sheet 1
      Next i
      .Range(.Cells(1, 6), .Cells(lastrow, 7)) = outarr  ' write out the output array
     
   End With
End Sub

 

[Khoong]

welcome to the forum, here is the super fast VBA way of dealing with this problem, my code uses a VBA dictionary and variant arrays which is a much faster way of doing this task than using the excel functions.

Sub dictionary()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
 
 
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Sheet2")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(lastrow, 4))
   End With
   For i = 2 To UBound(Ary)
    ' concatentate colums A and B
      temp = Ary(i, 1) & Ary(i, 2)
      Dic(temp) = i                 ' save array row in dictionary
   Next i
   With Worksheets("Sheet1")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      inarr = .Range(.Cells(1, 1), .Cells(lastrow, 5))
      outarr = .Range(.Cells(1, 6), .Cells(lastrow, 7))
  
      For i = 2 To lastrow
         temp = inarr(i, 1) & inarr(i, 5)  '     ' concatentate colums A and E
         indi = Dic(temp) 'this matches the value given by the index in the dictionary
         outarr(i, 1) = Ary(indi, 3) ' copy column C of sheet 2 to column F of sheet 1
         outarr(i, 2) = Ary(indi, 4) ' copy column D of sheet 2 to column G of sheet 1
      Next i
      .Range(.Cells(1, 6), .Cells(lastrow, 7)) = outarr  ' write out the output array
    
   End With
End Sub

Hi thank very much for your help. you really helping me alot as this is exactly what I need. I have spend almost month to search for this solution as I really new to VBA coding not sure whether what I ask for is a stupid question or not but still thanks for your help. By the way, I am still having some query about the code you provided. I am still having some questions.

1) I am not sure how indi = Dic(temp) works? you have store Dic(temp) with array row in dictionar. how about indi? as i did not see there is any line to assist value for indi
2) Is it a way for me to set a condition if the record in sheet1 is not appeard in sheet2 then the code will just skip the row. for exmaple like below picture stated

 

[Khoong]

Hi thank very much for your help. you really helping me alot as this is exactly what I need. I have spend almost month to search for this solution as I really new to VBA coding not sure whether what I ask for is a stupid question or not but still thanks for your help. By the way, I am still having some query about the code you provided. I am still having some questions.

1) I am not sure how indi = Dic(temp) works? you have store Dic(temp) with array row in dictionar. how about indi? as i did not see there is any line to assist value for indi
2) Is it a way for me to set a condition if the record in sheet1 is not appeard in sheet2 then the code will just skip the row. for exmaple like below picture stated

View attachment 72663

 

[offthelip]

VBA Dictionaries are a very powerful tool that can be used for lots of things, they don't related to anything that you can do on a worksheet. One way of thinking about them is that they are just like a single dimensional array with a index that instead of going 1,2,3,4,5 etc, they have an index that can take any variable, i.e a name, ( i.e John Mike, Alice, Jane... etc) a combination of two names or as I have done in your case a combination of two columns which can contain any text. Each element of the dictionary contains one value, in your case I have assigned the value to the row from sheet2 that matches the to rows on sheet2.
So the first bit of the code:

      temp = Ary(i, 1) & Ary(i, 2)
      Dic(temp) = i

in this loop I go down sheet2 a row at a time and concatenate the cell in column A with the cell in column B
I then use this temporary variable to create an element inthe dictionary with the index of temp and a value equal to the row it comes from (i)
This creates the "lookup table"
then the second half of the code on sheet1:

        temp = inarr(i, 1) & inarr(i, 5)  '     ' concatentate colums A and E
         indi = Dic(temp) 'this matches the value given by the index in the dictionary

Once again I create a temporary variable by concatenating the values from column A and column E
I then use this variable as the index into the dictionary that I created as the lookup table and return the value of that element of the dictionary which is the row that matches from sheet 2 and assign it to the varaible indi.
I then use the index indi to return the actual values from that row on sheet 2
It is very easy to check for the case where a value on sheet 1 doesn't exist on sheet2 : we check whether it exists!!! see this code modification:

Sub dictionary()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
 
 
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Sheet2")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(lastrow, 4))
   End With
   For i = 2 To UBound(Ary)
    ' concatentate colums A and B
      temp = Ary(i, 1) & Ary(i, 2)
      Dic(temp) = i                 ' save array row in dictionary
   Next i
   With Worksheets("Sheet1")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      inarr = .Range(.Cells(1, 1), .Cells(lastrow, 5))
      outarr = .Range(.Cells(1, 6), .Cells(lastrow, 7))
  
      For i = 2 To lastrow
         temp = inarr(i, 1) & inarr(i, 5)  '     ' concatentate colums A and E
         If Dic.Exists(temp) Then   ' check if element exists
         indi = Dic(temp) 'this matches the value given by the index in the dictionary
         outarr(i, 1) = Ary(indi, 3) ' copy column C of sheet 2 to column F of sheet 1
         outarr(i, 2) = Ary(indi, 4) ' copy column D of sheet 2 to column G of sheet 1
         Else          ' if it doesn't set to not found
         outarr(i, 1) = "Not Found"
         outarr(i, 2) = ""
         End If
        
      Next i
      .Range(.Cells(1, 6), .Cells(lastrow, 7)) = outarr  ' write out the output array
    
   End With
End Sub

 

[Khoong]

VBA Dictionaries are a very powerful tool that can be used for lots of things, they don't related to anything that you can do on a worksheet. One way of thinking about them is that they are just like a single dimensional array with a index that instead of going 1,2,3,4,5 etc, they have an index that can take any variable, i.e a name, ( i.e John Mike, Alice, Jane... etc) a combination of two names or as I have done in your case a combination of two columns which can contain any text. Each element of the dictionary contains one value, in your case I have assigned the value to the row from sheet2 that matches the to rows on sheet2.
So the first bit of the code:

      temp = Ary(i, 1) & Ary(i, 2)
      Dic(temp) = i

in this loop I go down sheet2 a row at a time and concatenate the cell in column A with the cell in column B
I then use this temporary variable to create an element inthe dictionary with the index of temp and a value equal to the row it comes from (i)
This creates the "lookup table"
then the second half of the code on sheet1:

        temp = inarr(i, 1) & inarr(i, 5)  '     ' concatentate colums A and E
         indi = Dic(temp) 'this matches the value given by the index in the dictionary

Once again I create a temporary variable by concatenating the values from column A and column E
I then use this variable as the index into the dictionary that I created as the lookup table and return the value of that element of the dictionary which is the row that matches from sheet 2 and assign it to the varaible indi.
I then use the index indi to return the actual values from that row on sheet 2
It is very easy to check for the case where a value on sheet 1 doesn't exist on sheet2 : we check whether it exists!!! see this code modification:

Sub dictionary()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
 
 
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Sheet2")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(lastrow, 4))
   End With
   For i = 2 To UBound(Ary)
    ' concatentate colums A and B
      temp = Ary(i, 1) & Ary(i, 2)
      Dic(temp) = i                 ' save array row in dictionary
   Next i
   With Worksheets("Sheet1")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      inarr = .Range(.Cells(1, 1), .Cells(lastrow, 5))
      outarr = .Range(.Cells(1, 6), .Cells(lastrow, 7))
 
      For i = 2 To lastrow
         temp = inarr(i, 1) & inarr(i, 5)  '     ' concatentate colums A and E
         If Dic.Exists(temp) Then   ' check if element exists
         indi = Dic(temp) 'this matches the value given by the index in the dictionary
         outarr(i, 1) = Ary(indi, 3) ' copy column C of sheet 2 to column F of sheet 1
         outarr(i, 2) = Ary(indi, 4) ' copy column D of sheet 2 to column G of sheet 1
         Else          ' if it doesn't set to not found
         outarr(i, 1) = "Not Found"
         outarr(i, 2) = ""
         End If
       
      Next i
      .Range(.Cells(1, 6), .Cells(lastrow, 7)) = outarr  ' write out the output array
   
   End With
End Sub

Hi thanks a lot for your explanation. If today I need to match 3 condition can I work the same way? Let use same sheet as example and I try to edit base on your code

Sub dictionary()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
 
 
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Sheet2")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(lastrow, 4))
   End With
   For i = 2 To UBound(Ary)
    ' concatentate colums A and B
      temp = Ary(i, 1) & Ary(i, 2) & Ary(i, 3)
      Dic(temp) = i                 ' save array row in dictionary
   Next i
   With Worksheets("Sheet1")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      inarr = .Range(.Cells(1, 1), .Cells(lastrow, 6))
      outarr = .Range(.Cells(1, 6), .Cells(lastrow, 7))
 
      For i = 2 To lastrow
         temp = inarr(i, 1) & inarr(i, 5) & inarr(i, 6)  '     ' concatentate colums A and E
         If Dic.Exists(temp) Then   ' check if element exists
         indi = Dic(temp) 'this matches the value given by the index in the dictionary
         outarr(i, 2) = Ary(indi, 4) ' copy column D of sheet 2 to column G of sheet 1
         Else          ' if it doesn't set to not found
         outarr(i, 1) = "Not Found"
         outarr(i, 2) = ""
         End If
       
      Next i
      .Range(.Cells(1, 6), .Cells(lastrow, 7)) = outarr  ' write out the output array
   
   End With
End Sub

 

[offthelip]

Yes you can use three or more columns concatenated together, the idea is the same. If you get to grips with using variant arrays and dictionaries in VBA you will be well on the way to being able to write super fast really powerful code. Many people suffer from writing code using the same functions and techniques that they would if doing it manually only to find the code is really really slow. Use variant array wherever you can to ensure your code is fast.