Index-Match VBA Code where result requires two matches in two different columns from the same row

[rokgod7]

I wrote this formula in Excel, and it works.

=INDEX(L:L,MATCH(U2&V2,I:I&D:D,0))

I tried several methods to write this into VBA, where all hard references are variables—ranges and search items. I also needed my search items to be de-formatted, so that even if I had them formatted as numbers or text, it would match. No matter what I did, I got error code 13. I finally gave up, and cheated by creating an extra column that concatenated the two items I wanted to search for independently, and did a XLookup off of that. But I feel dirty doing it. I know there's a better way using Index and Match.

 

[DanteAmor]

Hi and welcome to MrExcel.

where all hard references are variables—ranges and search items

You didn't write what you are storing in the variables.
Are you storing a text, the value of a cell, a range?


I show you 4 options, to see if one is what you need.

Sub Macro1()
  Dim IndexCol As String
  Dim val1 As String
  Dim val2 As String
  Dim arr1 As String
  Dim arr2 As String
 
  IndexCol = "L:L"
  val1 = "myvalue1"
  val2 = "myvalue2"
  arr1 = "I:I"
  arr2 = "D:D"
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol & ",MATCH(""" & val1 & """&""" & val2 & """," & arr1 & "&" & arr2 & ",0))"
End Sub

Sub Macro2()
  Dim IndexCol As String
  Dim cel1 As String
  Dim cel2 As String
  Dim arr1 As String
  Dim arr2 As String
 
  IndexCol = "L:L"
  cel1 = Range("U2").Value
  cel2 = Range("V2").Value
  arr1 = "I:I"
  arr2 = "D:D"
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol & ",MATCH(""" & cel1 & """&""" & cel2 & """," & arr1 & "&" & arr2 & ",0))"
End Sub

Sub Macro3()
  Dim IndexCol As Range
  Dim cel1 As Range
  Dim cel2 As Range
  Dim arr1 As Range
  Dim arr2 As Range
 
  Set IndexCol = Range("L:L")
  Set cel1 = Range("U2")
  Set cel2 = Range("V2")
  Set arr1 = Range("I:I")
  Set arr2 = Range("D:D")
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol.Address & ",MATCH(""" & cel1.Value & """&""" & cel2.Value & """," & arr1.Address & "&" & arr2.Address & ",0))"
End Sub

Sub Macro4()
  Dim IndexCol As Range
  Dim cel1 As Range
  Dim cel2 As Range
  Dim arr1 As Range
  Dim arr2 As Range
 
  Set IndexCol = Range("L:L")
  Set cel1 = Range("U2")
  Set cel2 = Range("V2")
  Set arr1 = Range("I:I")
  Set arr2 = Range("D:D")
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol.Address & ",MATCH(" & cel1.Address & "&" & cel2.Address & "," & arr1.Address & "&" & arr2.Address & ",0))"
End Sub

NOTE: Maybe in your 365 version, instead of FormulaArray you should just put Formula

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[bebo021999]

Once you have used VBA code to replace formulas, you should avoid using formulas within the code.
The following code snippet should be placed in the worksheet module (Right-click on the tab's name, select "View Code", and paste the code into the window).
It will automatically run whenever there is a manual change in values within the related ranges.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, rng, dic As Object, id As String, valu As String
Set dic = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "L").End(xlUp).Row
If Intersect(Target, Union(Range("D1:D" & lr), Range("I1:I" & lr), Range("L1:L" & lr), Range("U2:V2"))) _
Is Nothing Then Exit Sub
rng = Range("D1:L" & lr).value
valu = Range("U2") & "|" & Range("V2")
For i = 1 To UBound(rng)
    id = rng(i, 6) & "|" & rng(i, 1)
    If Not dic.exists(id) Then
        dic.Add id, rng(i, 9)
    End If
Next
Range("C2").value = IIf(dic.exists(valu), dic(valu), "Not match")
End Sub

 

[rokgod7]

Hi and welcome to MrExcel.



You didn't write what you are storing in the variables.
Are you storing a text, the value of a cell, a range?


I show you 4 options, to see if one is what you need.

Sub Macro1()
  Dim IndexCol As String
  Dim val1 As String
  Dim val2 As String
  Dim arr1 As String
  Dim arr2 As String
 
  IndexCol = "L:L"
  val1 = "myvalue1"
  val2 = "myvalue2"
  arr1 = "I:I"
  arr2 = "D:D"
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol & ",MATCH(""" & val1 & """&""" & val2 & """," & arr1 & "&" & arr2 & ",0))"
End Sub

Sub Macro2()
  Dim IndexCol As String
  Dim cel1 As String
  Dim cel2 As String
  Dim arr1 As String
  Dim arr2 As String
 
  IndexCol = "L:L"
  cel1 = Range("U2").Value
  cel2 = Range("V2").Value
  arr1 = "I:I"
  arr2 = "D:D"
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol & ",MATCH(""" & cel1 & """&""" & cel2 & """," & arr1 & "&" & arr2 & ",0))"
End Sub

Sub Macro3()
  Dim IndexCol As Range
  Dim cel1 As Range
  Dim cel2 As Range
  Dim arr1 As Range
  Dim arr2 As Range
 
  Set IndexCol = Range("L:L")
  Set cel1 = Range("U2")
  Set cel2 = Range("V2")
  Set arr1 = Range("I:I")
  Set arr2 = Range("D:D")
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol.Address & ",MATCH(""" & cel1.Value & """&""" & cel2.Value & """," & arr1.Address & "&" & arr2.Address & ",0))"
End Sub

Sub Macro4()
  Dim IndexCol As Range
  Dim cel1 As Range
  Dim cel2 As Range
  Dim arr1 As Range
  Dim arr2 As Range
 
  Set IndexCol = Range("L:L")
  Set cel1 = Range("U2")
  Set cel2 = Range("V2")
  Set arr1 = Range("I:I")
  Set arr2 = Range("D:D")
 
  Range("C2").FormulaArray = "=INDEX(" & IndexCol.Address & ",MATCH(" & cel1.Address & "&" & cel2.Address & "," & arr1.Address & "&" & arr2.Address & ",0))"
End Sub

NOTE: Maybe in your 365 version, instead of FormulaArray you should just put Formula

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Thanks so much!

 

[rokgod7]

Once you have used VBA code to replace formulas, you should avoid using formulas within the code.
The following code snippet should be placed in the worksheet module (Right-click on the tab's name, select "View Code", and paste the code into the window).
It will automatically run whenever there is a manual change in values within the related ranges.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, rng, dic As Object, id As String, valu As String
Set dic = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "L").End(xlUp).Row
If Intersect(Target, Union(Range("D1:D" & lr), Range("I1:I" & lr), Range("L1:L" & lr), Range("U2:V2"))) _
Is Nothing Then Exit Sub
rng = Range("D1:L" & lr).value
valu = Range("U2") & "|" & Range("V2")
For i = 1 To UBound(rng)
    id = rng(i, 6) & "|" & rng(i, 1)
    If Not dic.exists(id) Then
        dic.Add id, rng(i, 9)
    End If
Next
Range("C2").value = IIf(dic.exists(valu), dic(valu), "Not match")
End Sub

View attachment 97566

Thanks you very much.