Excel Formula or VBA: Find match's address in separate table with 2 column criteria - No helper columns

Question

I need help with formula structure,

I have 2 tables. I would like to find a match where column a & column b equal and get the address in table 2. They will be unique entries. So for example:

ProjectInfoTable :

         A      |       B      |     C     |
  ------------------------------------------
1 |     Name    |    Company   |  Project  |
  ------------------------------------------
2 | Chris Evans |     Apple    |   Info    |
  ------------------------------------------
3 | Chris Evans |    Google    |   Info    |
  ------------------------------------------
4 | Bill Gates  |  Burger King |   Info    |
  ------------------------------------------

ClientInfoTable:

         A      |       B      |   C   |   D
  -------------------------------------------
1 |    Client   |    Company   |  Age  |  Sex | 
  -------------------------------------------
2 | Chris Evans |     Apple    |   12  |   M  |
  -------------------------------------------
3 | Chris Evans |    Google    |   17  |   M  |
  -------------------------------------------
4 | Bill Gates  |  Burger King |   98  |   F  |
  -------------------------------------------

I would like to be able to, while in 'ProjectInfoTable', get the address of the matching Name & Company client in 'ClientInfoTable'

The trouble I'm running into is there could be a thousand different Chris Evans out there, so VLOOKUP isn't good here. I need to make sure it's the same Chris Evans in 'ClientInfoTable' that I'm looking at in 'ProjectInfoTable' by cross-referencing their companies

I can get the address no problem if I only search it by name:

=ADDRESS(ROW(INDEX(ClientInfoTable,MATCH([@[Client]],ClientInfoTable[Client],0),1)),COLUMN(INDEX(ClientInfoTable,MATCH([@[Client]],ClientInfoTable[Client],0),1)),1,1,"Clients")

but I need to add an additional condition of their companies so now that formula is useless.

Any ideas out there? I don't want to use hidden columns, or 'helper columns'

I will except VBA or formula based answers. I'll even reward a bounty to anyone who can provide both, assuming that the data will always be dynamic ranges, and then gives a good explanation of your code/formula. I am here to learn, I am not a copy/paste type of user, explanations go a long with me.

Answer 1

Formula:

Here's a formula only solution with no hidden/ helper columns and no array formulas:

=ADDRESS(
    ROW(
        ClientInfo
    ) - 1 +
    MATCH(
        1,
        INDEX(
            --INDEX(
                ClientInfo[Client] = $A5,
                0
            ) *
            --INDEX(
                ClientInfo[Company] = $B5,
                0
            ),
            0
        ),
        0
    ),
    COLUMN(ClientInfo)
)

Components:

a --INDEX(ClientInfo[Client]=$A5,0) - returns a boolean array for as many matches there are for e.g. Chris Evans in ClientInfo[Client]. In the example below this will be {TRUE,TRUE,FALSE,FALSE}. This is then cast to array of integer with the double unary operator to leave {1,1,0,0}

b --INDEX(ClientInfo[Company]=$B5,0) - same thing as a e.g. Apple in ClientInfo[Company] in the example is the array {TRUE,FALSE,FALSE,TRUE} - which is then cast to {1,0,0,1}

c INDEX(a*b,0) - multiples elements 1..n of array a with elements 1..n of array b. In our example this results in {1,0,0,0} and at this point you have identified your matching composite key for Chris Evans and Apple as being the 1st row of ClientInfo

d MATCH(1,c,0) - gets the index of the 1 in the array which in our example of Chris Evans and Apple is 1. You mentioned They will be unique entries so I don't think we have a problem here.

e ROW(ClientInfo)-1+d - I defined ClientInfo as a Table/ ListObject with a range of A8:D12 but the reference is giving back A9:D12 which seems to be the way it works the named ranges for Tables/ ListObjects. So we need to deduct one from the ROW of that range to get the start of the offset; then simply add the result of d.

f ADDRESS(e,COLUMN(ClientInfo)) - returns the cell address of e and the first column of ClientInfo table.

Example:

VBA:

Using the example above, the VBA method will do the following:

1. Assume no match found
2. Iterate the rows of the table
3. Get the candidate values and check against the inputs for the columns referenced
4. If both match then quit the loop returning the address

The code:

Option Explicit

Sub Test()

    MsgBox GetAddressOfKey("Client", "Chris Evans", "Company", "Apple", "ClientInfo")
    MsgBox GetAddressOfKey("Client", "Chris Evans", "Company", "Google", "ClientInfo")
    MsgBox GetAddressOfKey("Client", "Bill Gates", "Company", "Burger King", "ClientInfo")

End Sub

Function GetAddressOfKey(col1 As String, val1 As String, col2 As String, val2 As String, strTable As String) As String

    Dim lst As ListObject
    Dim lr As ListRow
    Dim strAddress As String
    Dim strCandidate1 As String
    Dim strCandidate2 As String

    strAddress = ""
    Set lst = ActiveSheet.ListObjects(strTable)

    'iterate rows
    For Each lr In lst.ListRows
        'get candidate values
        strCandidate1 = Intersect(lr.Range, lst.ListColumns(col1).Range).Value
        strCandidate2 = Intersect(lr.Range, lst.ListColumns(col2).Range).Value
        'check against inputs
        If strCandidate1 = val1 And strCandidate2 = val2 Then
            strAddress = lst.Range.Cells(lr.Index + 1, 1).Address
            'quit if we find a match
            Exit For
        End If
    Next lr

    'return
    GetAddressOfKey = strAddress

End Function

PS I was hesitant to provide the VBA answer as well as you already accepted a decent answer. However, I have a slight preference for doing this without updating the UI although I agree the AutoFilter method is good enough. HTH