Index Match to translate to VBA

[GonzoB]

Hi Helpers,

I have an index-match formula to program in VBA:
=INDEX(Export!$B$2:$H$1000,MATCH(1,(Export!$B$2=B2)*(Export!$H$2="US"),0),5)

I have two sheets in my excel, Export and Result. On tab Result I have a column (B2) with values which need to match with the same type of values from tab Export. This is the first criteria in the match formula. The second criteria is simple, the country need to be US.

I have 170 000 records on tab Export so I'm trying to find the most effective way to program this in VBA. I'm planning to turn off screen updating, automated calculations and events in the macro code. After browsing on forums the conclusion I came to is that in the VBA I need to use Listobjects to bring the data into the memory, instead of using the data on the Sheets. I was trying to program this but I'm new to VBA and got nowhere.

Can somebody help me with the script which does the work? It would be extremely helpful.
Thanks in advance.

 

[bebo021999]

Do you mean:
=INDEX(Export!$B$2:$H$1000,MATCH(1,(Export!$B$2:$B$1000=B2)*(Export!$H$2:$H$1000="US"),0),5)

Assum Sheets("Result") cell C2 contains result:

Sub test()
Dim Arr() As Variant, i, Lr As Long
With Sheets("Export")
Lr = .Cells(65000, "B").End(xlUp).Row
ReDim Arr(1 To Lr - 1, 1 To 7)
Arr = .Range("B2:H" & Lr).Value
    For i = 1 To UBound(Arr)
      If Arr(i, 1) = Sheets("Result").Range("B2").Value And Arr(i, 7) = "US" Then
         Sheets("Result").Range("C2").Value = Arr(i, 5)
      Exit For
      End If
        
    Next
End With
End Sub

 

[GonzoB]

Thank you for the Answer.

When running the code excel gives me error 'subscript out of range', which points to this row: ReDim Arr(1 To Lr - 1, 1 To 7)

Do you have an idea what's wrong?

 

[Fluff]

You can delete that line as it's not needed. Also change this line

Lr = .Cells(65000, "B").End(xlUp).Row

to

Lr = .Cells(Rows.Count, "B").End(xlUp).Row

 

[Alex Blakenburg]

Assuming your result sheet has a list of values in Column B, try this:-

Sub LookupValue()

    Dim shtExport As Worksheet
    Dim shtResult As Worksheet
    Dim aryExport As Variant
    Dim aryResult As Variant
    Dim rngResult As Range
    Dim i As Long, j As Long
    Dim strCountry As String
    
    strCountry = "US"

    Set shtExport = Worksheets("Export")
    Set shtResult = Worksheets("Result")
    
    aryExport = shtExport.Range("B2:H" & shtExport.Cells(Rows.Count, "B").End(xlUp).Row).Value
    Set rngResult = shtResult.Range("B2:C" & shtResult.Cells(Rows.Count, "B").End(xlUp).Row)
    aryResult = rngResult.Value
    
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.dictionary")
    For i = 1 To UBound(aryExport, 1)
        If aryExport(i, 7) = strCountry Then
              Dic(aryExport(i, 1)) = aryExport(i, 5)
        End If
    Next i
    
    For j = 1 To UBound(aryResult, 1)
        If Dic.exists(aryResult(j, 1)) Then
            aryResult(j, 2) = Dic(aryResult(j, 1))
        Else
            aryResult(j, 2) = ""
        End If
    Next j
    
    rngResult = aryResult
 
End Sub

 

[GonzoB]

Thank you for the tips.

After making some changes in my excel file my VBA code looks like this:

Dim Arr() As Variant, p, Lr As Long
With Sheets("Export")
Lr = .Cells(Rows.Count, "A").End(xlUp).Row
Arr = .Range("A2:G" & Lr).Value
For p = 1 To UBound(Arr)
If Arr(p, 1) = Sheets("Result").Range("B2").Value And Arr(p, 7) = "US" Then
Sheets("Result").Range("C2:C2000").Value = Arr(p, 2)
End If

Next

End With


My issue now is that into all the Result sheet C column cells the same values gets copied although the the Export sheet records satisfying the 2 criterion have different values in the second column. I cannot find the reason. Any idea?

 

[Alex Blakenburg]

My issue now is that into all the Result sheet C column cells the same values gets copied although the the Export sheet records satisfying the 2 criterion have different values in the second column. I cannot find the reason. Any idea?

I appreciate that you find just using arrays easier to undertand but the code you are using is looping through the Export 170k records to find the Results value in B2. To find all the values for Column B in the Result sheet you would need a 2nd loop, looping through all the values in Result. This potentially means looping through 170k x 2k records and would take minutes instead of seconds to run.

Using a dictionary would be a better option. Did you try my code ?

Here is 1 link if you want to read up on it:
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

 

[bebo021999]

Follw the first formula index(match, i guess you wish to return a single first result match only. If yes, using loop is acceptable, by "exit for" right after first matching. If
your goal is to extract a list, dictionary with no loop is best choice.

 

[GonzoB]

I appreciate that you find just using arrays easier to undertand but the code you are using is looping through the Export 170k records to find the Results value in B2. To find all the values for Column B in the Result sheet you would need a 2nd loop, looping through all the values in Result. This potentially means looping through 170k x 2k records and would take minutes instead of seconds to run.

Using a dictionary would be a better option. Did you try my code ?

Here is 1 link if you want to read up on it:
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

I appreciate that you find just using arrays easier to undertand but the code you are using is looping through the Export 170k records to find the Results value in B2. To find all the values for Column B in the Result sheet you would need a 2nd loop, looping through all the values in Result. This potentially means looping through 170k x 2k records and would take minutes instead of seconds to run.

Using a dictionary would be a better option. Did you try my code ?

Here is 1 link if you want to read up on it:
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

Hi Alex,

thanks for the code. I tried it and it does not populate the target field on the Results tab. Do you know why?

I can see the code matching the specific Export tab field against "US" but I cannot see where the matching happens between Export A column and Results B column values. Is it possible that the the target cells are left empty because of this part of the code?

For j = 1 To UBound(aryResult, 1)
If Dic.exists(aryResult(j, 1)) Then
aryResult(j, 2) = Dic(aryResult(j, 1))
Else
aryResult(j, 2) = ""
End If
Next j

aryResult = rngResult.Value


Thanks in advance.

 

[Alex Blakenburg]

Did you copy in the code I supplied ?