VBA Match function for two criteria / columns

[LancashirePoacher]

Seen many close answers to my simple question, but is there an elegant way to use the Application.Match function for 2 criteria to match against 2 columns

key1 = "QI"
key2 = "Episode"

keycol1 = 2
keycol2 = 6

application.match on 1 criteria works perfectly

row = Application.Match(key1, ActiveSheet.Columns(keycol1), 0)

but

row = Application.Match(key1&key2, ActiveSheet.Columns(keycol1) & ActiveSheet.Columns(keycol2), 0)

fails error 2029 - trying to concatentate two ranges?

I can get it to work using Evaluate

row = Evaluate("MATCH(""" & key1 & """&""" & key2 & """, $B$2:$B$500 & $F$2:$F$500, 0)")

but its not very elegant and probably slower.

 

[Joe4]

Welcome to the Board!

You may want to take a look at these posts, where people asked a similar question about using VBA to match on 2 columns:

VBA Excel To Match 2 columns on sheet A with 2 columns on sheet B and return value

I need a VBA Excel Script To Compare columns A & B in Worksheet One Against A & B In worksheet Two If A Match Is Found Return column C from Worksheet One. Ive can do this in excel using a formula but it is speed im after so hopefully doing via VBA would be quicker and id prefer the final output...

www.mrexcel.com

VBA Match Statement With Two Conditions?

I am looking for a VBA solution to find the row number in which two conditions exist. (Match function perhaps ... I've only ever used it with one condition) Condition one , column H must equal variable pnum AND Condition two, column B must equal variable facname

www.mrexcel.com

 

[Domenic]

How about something like this?

Option Explicit

Sub test()

    Dim key1 As String
    Dim key2 As String
    Dim keyCol1 As Long
    Dim keyCol2 As Long
    Dim range1 As Range
    Dim range2 As Range
    Dim row As Variant

    key1 = "QI"
    key2 = "Episode"
  
    keyCol1 = 2
    keyCol2 = 6
  
    With ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly
        Set range1 = Application.Intersect(.Columns(keyCol1), .UsedRange)
        Set range2 = Application.Intersect(.Columns(keyCol2), .UsedRange)
    End With
  
    row = match2(key1, key2, range1, range2)
  
    If Not IsError(row) Then
        MsgBox "Match found on row " & row, vbInformation
    Else
        MsgBox "Match not found.", vbInformation
    End If
  
End Sub

Function match2(ByVal key1 As String, ByVal key2 As String, ByVal keyRange1 As Range, ByVal keyRange2 As Range) As Variant

    Dim rowIndex As Long
  
    For rowIndex = 1 To keyRange1.Rows.Count
        If (keyRange1(rowIndex, 1).Value = key1) And (keyRange2(rowIndex, 1).Value = key2) Then
            match2 = rowIndex
            Exit Function
        End If
    Next rowIndex
  
    match2 = CVErr(xlErrNA)
  
End Function

Hope this helps!

 

[LancashirePoacher]

Welcome to the Board!

You may want to take a look at these posts, where people asked a similar question about using VBA to match on 2 columns:

VBA Excel To Match 2 columns on sheet A with 2 columns on sheet B and return value

I need a VBA Excel Script To Compare columns A & B in Worksheet One Against A & B In worksheet Two If A Match Is Found Return column C from Worksheet One. Ive can do this in excel using a formula but it is speed im after so hopefully doing via VBA would be quicker and id prefer the final output...

www.mrexcel.com

VBA Match Statement With Two Conditions?

I am looking for a VBA solution to find the row number in which two conditions exist. (Match function perhaps ... I've only ever used it with one condition) Condition one , column H must equal variable pnum AND Condition two, column B must equal variable facname

www.mrexcel.com

Thanks Joe4 - I had seen these.

Link 1 solves a slightly different problem and, unless I'm mistaken, the solution in Link 2 fails where there are duplicate entries in either column matching the required key for that column - ie the MATCH finds only the first entry for the criteria, not all entries as per an array match.

 

[Joe4]

Did you try Domenic's code he posted to this thread?

 

[LancashirePoacher]

How about something like this?

Option Explicit

Sub test()

    Dim key1 As String
    Dim key2 As String
    Dim keyCol1 As Long
    Dim keyCol2 As Long
    Dim range1 As Range
    Dim range2 As Range
    Dim row As Variant

    key1 = "QI"
    key2 = "Episode"
 
    keyCol1 = 2
    keyCol2 = 6
 
    With ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly
        Set range1 = Application.Intersect(.Columns(keyCol1), .UsedRange)
        Set range2 = Application.Intersect(.Columns(keyCol2), .UsedRange)
    End With
 
    row = match2(key1, key2, range1, range2)
 
    If Not IsError(row) Then
        MsgBox "Match found on row " & row, vbInformation
    Else
        MsgBox "Match not found.", vbInformation
    End If
 
End Sub

Function match2(ByVal key1 As String, ByVal key2 As String, ByVal keyRange1 As Range, ByVal keyRange2 As Range) As Variant

    Dim rowIndex As Long
 
    For rowIndex = 1 To keyRange1.Rows.Count
        If (keyRange1(rowIndex, 1).Value = key1) And (keyRange2(rowIndex, 1).Value = key2) Then
            match2 = rowIndex
            Exit Function
        End If
    Next rowIndex
 
    match2 = CVErr(xlErrNA)
 
End Function

Hope this helps!

Thanks Domenic - I'm sure this works, but I was trying to solve it using internal vba functions (ie - my "elegant" criteria), rather than creating expanded code.

As an aside, any ideas how convert a range to a text string - eg ActiveSheet.Columns(1) to "$A1:$A500", where lastrow in ActiveSheet is 500 - again I can do it long hand but is there a vba method/property/function that does it quicker?

 

[Domenic]

Thanks Domenic - I'm sure this works, but I was trying to solve it using internal vba functions (ie - my "elegant" criteria), rather than creating expanded code.

Sorry, but I'm not sure what you mean. Can you please elaborate?

As an aside, any ideas how convert a range to a text string - eg ActiveSheet.Columns(1) to "$A1:$A500", where lastrow in ActiveSheet is 500 - again I can do it long hand but is there a vba method/property/function that does it quicker?

Maybe something like this?

    Dim addr As String
    
    With ActiveSheet
        addr = Application.Intersect(.Columns(1), .UsedRange).Address(0, 1)
    End With

    MsgBox addr, vbInformation

 

[LancashirePoacher]

Sorry, but I'm not sure what you mean. Can you please elaborate?


Maybe something like this?

    Dim addr As String
   
    With ActiveSheet
        addr = Application.Intersect(.Columns(1), .UsedRange).Address(0, 1)
    End With

    MsgBox addr, vbInformation

I was looking for a vba version of the excel MATCH function, which at it's heart uses boolean logic on a 2 criteria match.

=MATCH(1,(H5=B5:B11)*(H6=C5:C11),0)

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

Thanks for the .address pointer - i'll try it out on a range...

 

[LancashirePoacher]

Thanks Domenic for the .address pointer...

I'm happy with this solution

RangeString = ActiveSheet.Columns(keycol1).Address & " & " & ActiveSheet.Columns(keycol2).Address
Formula = "MATCH(""" & key1 & """&""" & key2 & """, " & RangeString & ", 0)"

row = Evaluate(Formula)

or as one long line

row = Evaluate("MATCH(""" & key1 & """&""" & key2 & """, " & ActiveSheet.Columns(keycol1).Address & " & " & ActiveSheet.Columns(keycol2).Address & ", 0)")

For background, this is part of my project that updates iTunes metadata for Music, Films and TVShows from Data copied into excel from Wikipedia etc... and not all TV Show episodes have unique titles - so I wanted to get the correct data for QI "Pilot" episode vs Ted Lasso "Pilot" episode!!!

Key1 = "Pilot", keycol1 = 1 ("Name" column in Excel)
Key2 = "Ted Lasso", keycol2 = 2 ("Show" column in Excel)

Works perfectly - I can now watch all correctly labelled episodes of Ted Lasso! (I'm sure you wanted to know all that!)

 

[Domenic]

That's great, I'm glad you have a solution that you're happy with.

Cheers!