title: VBA (Excel): Find Based on Multiple Search Criteria Without Looping
body: <p><strong>I have a large data sheet that I want to search in VBA based on 3 sets of criteria</strong>. Each row entry can be assumed to be unique. The format of the sheet/data itself cannot be changed due to requirements. (I've seen several posts on related questions but haven't found a working solution for this yet.)</p>
  
  <p>At first I used the classic VBA <a href="http://msdn.microsoft.com/en-us/library/office/ff839746.aspx" rel="nofollow">find</a> method in a loop:</p>
  
  <pre><code>Set foundItem = itemRange.Find(What:=itemName, Lookin:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
  If Not foundItem Is Nothing Then
      firstMatchAddr = foundItem.Address
      Do
          ' *Check the other fields in this row for a match and exit if found*
          Set foundItem = itemRange.FindNext(foundItem)
      Loop While foundItem.Address &lt;&gt; firstMatchAddr  And Not foundItem Is Nothing
  End If
  </code></pre>
  
  <p>But because this needs to be called a number of times on large sets of data, the speed of this was no good. </p>
  
  <p>I did some searching and found that I could use the <a href="http://msdn.microsoft.com/en-us/library/office/ff835873.aspx" rel="nofollow">match</a> method with <a href="http://msdn.microsoft.com/en-us/library/office/ff197581.aspx" rel="nofollow">index</a>. So I unsuccessfully tried many variations of that such as:</p>
  
  <pre><code>result = Evaluate("=MATCH(1, (""" &amp; criteria1Name &amp; """=A2:A" &amp; lastRow &amp; ")*(""" &amp; criteria2Name &amp; """=B2:B" &amp; lastRow &amp; ")*(""" &amp; criteria3Name &amp; """=C2:C" &amp; lastRow &amp; "), 0)")
  </code></pre>
  
  <p>And</p>
  
  <pre><code>result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Name = criteria1Range)*(criteria2Name = criteria2Range)*(criteria3Name = criteria3Range))
  </code></pre>
  
  <p>And</p>
  
  <pre><code>result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Range=criteria1Name )*(criteria2Range=criteria2Name )*(criteria3Range=criteria3Name ))
  </code></pre>
  
  <p>Then I tried using <a href="http://msdn.microsoft.com/en-us/library/office/ff193884.aspx" rel="nofollow">AutoFilter</a> to sort:</p>
  
  <pre><code>.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=1, Criteria1:="=" &amp; criteria1Name
  .Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=2, Criteria1:="=" &amp; criteria2Name
  .Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=3, Criteria1:="=" &amp; criteria3Name
  </code></pre>
  
  <p>But because one of the sorting columns contains dates, I had issues getting AutoFilter to work properly. </p>
  
  <p>My question is, <strong>how can I search through columns in Excel VBA based on multiple criteria, <em>without looping</em>, returning either the row number or the value in the cell of that row that I am interested in</strong>?</p>