Index-Match through VBA code

[Arpita]

Hi,

I am trying to put Index-Match formula on my data through VBA code, but I am unable to create the dynamic ranges and use index match though VBA.

Formula in excel looks like this:

=IFERROR((IF(INDEX(Answers!$A$8:$O$13,MATCH($A2,Answers!$A$8:$A$13,0),MATCH($J2,Answers!$A$8:$O$8,0))=$K2,1,0)),0)

Any help would be greatly appreciated! Thanks in advance.

Arpita

 

[Luke M]

Welcome to the forum, Arpita!

Not sure what you were trying before, but the code would be something like:

Sub Example()

Range("B2:B100").Formula = _
    "=IFERROR((IF(INDEX(Answers!$A$8:$O$13,MATCH($A2,Answers!$A$8:$A$13,0),MATCH($J2,Answers!$A$8:$O$8,0))=$K2,1,0)),0)"


End Sub

 

[YasserKhalil]

Try this

Sub TestRun()
    Dim LR As Long, Rng As Range
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = Range("A8:O" & LR)
   
    If Range("K2").Value = GetData(Range("A2").Value, Range("J2").Value, Rng) Then
        MsgBox 1
    Else
        MsgBox 0
    End If
End Sub

Function GetData(Row_Name As String, Col_Name As String, Table_Array As Range) As Variant
    Dim rowRng As Range, colRng As Range
    Dim rowIndex As Long, colIndex As Long
    Row_Name = CStr(Row_Name): Col_Name = CStr(Col_Name)

    On Error Resume Next
    rowIndex = Application.Match(Row_Name, Table_Array.Columns(1), 0)
    colIndex = Application.Match(Col_Name, Table_Array.Rows(1), 0)
    On Error GoTo 0
    If rowIndex = 0 Or colIndex = 0 Then

        GetData = CVErr(xlErrRef)
    Else

        Set rowRng = Table_Array.Rows(rowIndex)
        Set colRng = Table_Array.Columns(colIndex)
        GetData = Table_Array.Parent.Evaluate("=" & rowRng.Address & " " & colRng.Address)
    End If
End Function

 

[Arpita]

Thank you so much @Luke M , It worked well
But I am really not sure how do I convert it into a dynamic formula like if I need to make the ranges dynamic.

 

[Arpita]

hi @YasserKhalil , This code looks really helpful and dynamic but I am getting error when I am trying to run this, error is at line no. 5 (where the function "Get Data" has been called).

Not sure if there is some problem with understanding :-(

 

[YasserKhalil]

Hi Arpita
Please attach your sample workbook to check the error
As for me no errors in this udf function.
I'm using it most of the time

 

[Luke M]

I'm not sure which range you want to make dynamic. The range where formula is going, or where it's looking at?

 

[Arpita]

Hi @Luke M and @YasserKhalil ..my formula is working now.
I used excel Named Ranges to define the range from where this formula is matching the values.
Thank you for the help