How to find a matching row in Excel?

Given

A search key

---------------------------
|   |  A  | B | C |   D   开发者_Python百科|
---------------------------
| 1 | 01  | 2 | 4 | TextA |
---------------------------

An Excel sheet

------------------------------
|   |  A | B | C |   D   | E |
------------------------------
| 1 | 03 | 5 | C | TextZ |   |
------------------------------
| 2 | 01 | 2 | 4 | TextN |   |
------------------------------
| 3 | 01 | 2 | 4 | TextA |   |
------------------------------
| 4 | 22 | T | N | TextX |   |
------------------------------

Question

I would like to have a function like this: f(key) -> result_row.

That means: given a search key (01, 2, 4, TextA), the function should tell me that the matching row is 3 in the example above.

The values in the search key (A, B, C, D) are a unique identifier.

How do I get the row number of the matching row?

One solution

The solution that comes first to my mind is to use Visual Basic for Application (VBA) to scan column A for "01". Once I've found a cell containing "01", I'd check the adjacent cells in columns B, C and D whether they match my search criteria.

I guess that algorithm will work. But: is there a better solution?

Version

• Excel 2000 9.0.8961 SP3
• VBA 6.5

However, if you happen to know a solution in any higher versions of Excel or VBA, I am curious to know as well.

Edit: 22.09.2010

Thank you all for your answers. @MikeD: very nice function, thank you!

My solution

Here is the solution I prototyped. It's all hard-coded, too verbose and not a function as in MikeD's solution. But I'll rewrite it in my actual application to take parameters and to return a result value.

Sub FindMatchingRow()
    Dim searchKeyD As Variant
    Dim searchKeyE As Variant
    Dim searchKeyF As Variant
    Dim searchKeyG As Variant

    Const indexStartOfRange As String = "D6"
    Const indexEndOfRange As String = "D9"

    ' Initialize search key
    searchKeyD = Range("D2").Value
    searchKeyE = Range("E2").Value
    searchKeyF = Range("F2").Value
    searchKeyG = Range("G2").Value


    ' Initialize search range
    myRange = indexStartOfRange + ":" + indexEndOfRange

    ' Iterate over given Excel range
    For Each myCell In Range(myRange)

        foundValueInD = myCell.Offset(0, 0).Value
        foundValueInE = myCell.Offset(0, 1).Value
        foundValueInF = myCell.Offset(0, 2).Value
        foundValueInG = myCell.Offset(0, 3).Value

        isUnitMatching = (searchKeyD = foundValueInD)
        isGroupMatching = (searchKeyE = foundValueInE)
        isPortionMatching = (searchKeyF = foundValueInF)
        isDesignationMatching = (searchKeyG = foundValueInG)
        isRowMatching = isUnitMatching And isGroupMatching And isPortionMatching And isDesignationMatching

        If (isRowMatching) Then
            Range("D21").Value = myCell.Row
            Exit For
        End If

    Next myCell
End Sub

This is the Excel sheet that goes with the above code:

here's a small VBA function I often use for that kind of purpose

Function FindInRange(InRange As Range, Arg As Range) As Integer
Dim Idx As Integer, Jdx As Integer, IsFound As Boolean

    FindInRange = 0
    IsFound = False

    For Idx = 1 To InRange.Rows.Count
        IsFound = True
        For Jdx = 1 To InRange.Columns.Count
            If InRange(Idx, Jdx) <> Arg(1, Jdx) Then
                IsFound = False
                Exit For
            End If
        Next Jdx

        If IsFound Then
            FindInRange = Idx
            Exit For
        End If
    Next Idx

End Function

InRange must be same width or wider than Arg, but can be of course larger or smaller than your A:D

In your Sample sheet enter in an empty cell "=findinrange(A1:D4,A3:D3)"

The formula will return "0" if nothing found, else the row #

Good luck - MikeD

Assume your search key starts in A1 and your data starts in A3. This array formula will return the row number where all the data matches the search key

=SUM(($A$3:$A$6=A1)*($B$3:$B$6=B1)*($C$3:$C$6=C1)*($D$3:$D$6=D1)*(ROW($A$3:$A$6)))

Enter with Control+Shift+Enter, not just Enter. Note that it returns the worksheet row, not the position in the table. If you want the position in the table, you could subtract one less than the starting row of the table from the result (2 in this case because the table starts on row 3).

If there are more than one rows that match, this will return the sum of all the rows (not very helpful). But I assumed there was only one matching row.

Another option is to add a new column to your excel sheet where the columns A:D are concatenated, and then use the lookup/sverweis function. This would be probably faster than a VBA solution.