How to find a suitable row in Excel?

Question

Considering

Search key

---------------------------
|   |  A  | B | C |   D   |
---------------------------
| 1 | 01  | 2 | 4 | TextA |
---------------------------

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.

This means: using the search key (01, 2, 4, TextA), the function should tell me that the corresponding line is 3 in the above example.

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

How to get the line number of the corresponding line?

One solution

The solution that comes to my mind is to use Visual Basic for Application (VBA) to scan column A for "01". Once I found a cell containing “01,” I would check the neighboring cells in columns B, C, and D to see if they match my search criteria.

I think this algorithm will work. But: is there a better solution?

Version

• Excel 2000 9.0.8961 SP3
• Vba 6.5

, Excel VBA, .

: 22.09.2010

. @MikeD: , !

, . , , , MikeD. , .

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

Excel, :

Answer 1

VBA,

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 , Arg, , , A: D

"= findinrange (A1: D4, A3: D3)"

"0", , #

- MikeD

Answer 2

, A1, A3. ,

=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)))

Control + Shift + Enter, Enter. , , . , , (2 , 3).

, ( ). , .

Answer 3

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