title: How to find a matching row in Excel?
body: <p><strong>Given</strong></p>
  
  <p>A search key</p>
  
  <pre><code>---------------------------
  |   |  A  | B | C |   D   |
  ---------------------------
  | 1 | 01  | 2 | 4 | TextA |
  ---------------------------
  </code></pre>
  
  <p>An Excel sheet</p>
  
  <pre><code>------------------------------
  |   |  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 |   |
  ------------------------------
  </code></pre>
  
  <p><strong>Question</strong></p>
  
  <p>I would like to have a function like this: f(key) -> result_row.</p>
  
  <p>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.</p>
  
  <p>The values in the search key (A, B, C, D) are a unique identifier.</p>
  
  <p>How do I get the row number of the matching row?</p>
  
  <p><strong>One solution</strong></p>
  
  <p>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.</p>
  
  <p>I guess that algorithm will work. But: is there a better solution?</p>
  
  <p><strong>Version</strong></p>
  
  <ul>
  <li>Excel 2000 9.0.8961 SP3</li>
  <li>VBA 6.5</li>
  </ul>
  
  <p>However, if you happen to know a solution in any higher versions of Excel or VBA, I am curious to know as well.</p>
  
  <p><strong>Edit: 22.09.2010</strong></p>
  
  <p>Thank you all for your answers. @MikeD: very nice function, thank you!</p>
  
  <p><strong>My solution</strong></p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is the Excel sheet that goes with the above code:</p>
  
  <p><img src="https://i.stack.imgur.com/ITlO6.png" alt="alt text"></p>