Use Ranged Match or Countif on dual columns

[HansSchulze]

The Countif below should read something like

...partNumber & companyName, Range("MPN!PDPMPN[MFR_PART_NUMBER] & Range("MPN!PDPMPN[ORGANIZATION_NAME")

to concatenate the two fields for the search. Unfortunately, MPN table columns are A and D are not consecutive.

    Dim company As Variant, rDataMart As Range, rDataMart2, where As Variant
    Set rDataMart = ThisWorkbook.Worksheets("MPN").Range("MPN!PDPMPN[MFR_PART_NUMBER]")
    Set rDataMart2 = ThisWorkbook.Worksheets("MPN").Range("MPN!PDPMPN[ORGANIZATION_NAME]")
    GetNearestCompany = ""
    For Each company In NameArray
        where = Application.CountIfs(rDataMart, MPN, rDataMart2, company) ' still not working
        If where Then
            If GetNearestCompany = Empty Then
                Debug.Print "Found", MPN, company, where
                GetNearestCompany = where
            Else
                Debug.Print "Dup", MPN, company, GetNearestCompany, where
            End If
        End If
    Next company
    If GetNearestCompany = Empty Then
        Debug.Print "NF", MPN, company
    End If

Thanks for your help in advance.

 

[HansSchulze]

It may be a datatypes issue. Latest try is from another post's suggestion using Match and Index, but it seems that Index is originally recognized by mouseover help, but goes "index" after I finish, and claims Type Mismatch error.

 Function GetNearestCompany(MPN As Variant, MySupplier As Variant) As Variant
dim where as Variant
...
where = Application.WorksheetFunction.Match(1, Application.WorksheetFunction.index((rDataMart = MPN) * (rDataMart2 = company), 0), 0)

which resembles this code for Excel: =MATCH(1,INDEX(($A$1:$A$6="id2")*($B$1:$B$6="day1"),),0)

What's the right syntax?

 

[HansSchulze]

Works with lots of digging on this and other excel forums.
Basically each search (A=RANGEA) returns an array of true/false, which can be multiplied by (B=RANGEB), gives an array of zeros, and just a few ones where the data matches, then Match looks for the first 1.

    Dim company As Variant, rDataMart As Range, rDataMart2, where As Variant, what As Variant
    Set rDataMart = ThisWorkbook.Worksheets("MPN").Range("MPN!PDPMPN[MFR_PART_NUMBER]")
    Set rDataMart2 = ThisWorkbook.Worksheets("MPN").Range("MPN!PDPMPN[ORGANIZATION_NAME]")
    GetNearestCompany = ""
    For Each company In NameArray
        what = "=Match(1,--(""" & MPN & """=" & rDataMart.Address & ") * --(""" & company & """=" & rDataMart2.Address & "), false)"
        where = Evaluate(what)
        If Not IsError(where) Then
            If GetNearestCompany = Empty Then
                Debug.Print "Found", MPN, company, where
                GetNearestCompany = where
            Else
                Debug.Print "Dup", MPN, company, GetNearestCompany, where
            End If
        End If
    Next company