Excel Formula or VBA: Find match's address in

问题:

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.

回答1:

you could use AutoFilter():

Option Explicit

Sub main()
    Dim rng As Range

    With ActiveSheet.ListObjects("ClientInfoTable").Range '<--| reference 'ClientInfoTable' range
        .AutoFilter field:=1, Criteria1:="Chris Evans" '<--| filter it on its 1st column with "Chris Evans"
        .AutoFilter field:=2, Criteria1:="Google" '<--| filter it on its 2nd column with "Google"
        If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then
            Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1) '<--| if any filtered cells other than header then set the range corresponding to upleftmost filtered cell below the headers row
            MsgBox "Found at " & rng.Address
        End If
        .AutoFilter '<--| show rows back visible
    End With
End Sub

回答2:

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

回答3:

One way to approach your problem is to use the SQL support in Excel VBA via ADODB. This Microsoft article explains how to do this.

Using the SQL support, you basically have two options: either you write a functions in VBA that returns the (first) value for a given name and company from the ClientInfoTable, using a SELECT statement, or you write a sub in VBA that directly inserts the values for all row in ProjectInfoTable wherever you need them, using a join between the ProjectInfoTable and the ClientInfoTable on the name and the company.

回答4:

You can also use the function I wrote.Put it to any module in the file where you want to use:

Option Compare Text
Option Explicit
Public Function doubleMatch(criteria1 As Range, criteria1_Range As Range, criteria2 As Range, criteria2_Range As Range) As Long
Dim crit1 As Variant, crit2 As Variant
Dim crit1_ARR(), crit2_ARR() 'Arrays where criteria ranges are stored
Dim i As Long, j As Long
Dim u1 As Long, l1 As Long

crit1_ARR = criteria1_Range.Value2
crit2_ARR = criteria2_Range.Value2
crit1 = criteria1.Value
crit2 = criteria2.Value

doubleMatch = -1
'checking if ranges have the same height
If (UBound(crit1_ARR) <> UBound(crit2_ARR)) Then
    GoTo endFunc
End If

'checking if ranges are one col wide
If (LBound(crit1_ARR, 2) <> UBound(crit1_ARR, 2)) Then
    GoTo endFunc
End If

If (LBound(crit2_ARR, 2) <> UBound(crit2_ARR, 2)) Then
    GoTo endFunc
End If

l1 = LBound(crit1_ARR)
u1 = UBound(crit1_ARR)

For i = l1 To u1
    If (crit1 = crit1_ARR(i, 1)) Then
        If (crit2 = crit2_ARR(i, 1)) Then
            doubleMatch = i + Abs(l1 = 0) * 1
            GoTo endFunc
        End If
    End If
Next i

endFunc:

End Function

Example of usage:

In case of error it returns -1; otherwise it returns number of row where it matches.

If you want the full address, you can use this function together with ADDRESS and MATCH functions:

=ADDRESS(doubleMatch(H2;A:A;I2;B:B);MATCH("Client";A:A;0))

回答5:

You can combine VLookUp with Choose to have more than one criteria. In your example:

=VLookUp(A1&","&B1, Choose({1.2}, ClientInfoTable!A1:A4&","&ClientInfoTable!B1:B4,ClientInfoTable!C1:C4), 2, False)

This is a matrix formula so you have to close it with Ctrl+Shift+Enter.

What happens is the following: the choose-function chooses the first and second value that it is given (that's what the first argument {1.2} says). As this is a matrix formula you get not only one pair of values but one for each row. In this example you get a matrix:

A-----------------------|B----------
Chris Evans, Apple      |12
Chris Evans, Google     |17
Bill Gates, Burger King |98

This is the matrix you can now use for VLookUp.

You should make sure that the delimiter (comma in this example) does not occur in your data.

If you only need the address and not the actual values, use the function Match instead of vlookup:

=Match(A1&","B1, Choose({1.2}, ClientInfoTable!A1:A4&","&B1:B4,ClientInfoTable!C1:C4))

This returns the row the corresponding set is in within the given matrix. If the matrix does not begin in row 1 you have to add the matrix-position.

回答6:

Create a VBA function that takes the clientname and company as string arguments - then loops through the clients table and return the address if found. Below code will return "Not found#" if there is no match

Option Explicit
Option Compare Text
Public Function SearchAddress(clientName As String, company As String) As String
    Dim x As Integer
    Dim shtClients As Worksheet
    Set shtClients = Sheets("Clients") 'Clients = name of the sheet with ClientTableInfo
    x = 2

    'loop through the clients table searching for the row where the clientname and companyname is what is supplied to the function

    Do
        'Column A has ClientName of ClientTableInfo
        'Column B has companyName of ClientTableInfo
        '.............
        'Column E has Address of ClientTableInfo <-- our search target
        If shtClients.Range("A" & x).Value = clientName And shtClients.Range("B" & x).Value = company Then
            SearchAddress = shtClients.Range("E" & x).Value 'column E has the address in the clients info table
            Exit Function
        End If
        x = x + 1
    Loop Until x > shtClients.UsedRange.Rows.Count
    SearchAddress = "Not found#"
End Function

To use this function import the code into a macro workbook as a VBA module (remember to enable macros). Then type the formula into an cell like a normal excel formula

=SearchAddress(ProjectInfoTable[@Name],ProjectInfoTable[@Company])

or

=SearchAddress(A1,A2)