title: VBA application.match does not find all values in lookup range
body: <p>I've looked around but no posts about a peculiar problem I am having. I am trying to find a combination of GGID (id) and country in a row. In my sheet, there are 4 rows with a given GGID but with different countries, then over 1000 rows with other ggids and then the correct GGID and correct country.</p>
  
  <p>The function looks like this:</p>
  
  <pre><code>Function rangefinder2(ByVal ggid As Long, ByRef sheet As Worksheet, ByVal country) As Object
  
  Dim wbMain As Workbook
  Set wbMain = ThisWorkbook
  Dim loopback As Long
  Dim x As Integer, y As Integer, z As Long
  Dim adresik As Long
  loopback = 1
  
      For x = 1 To 15
          If sheet.Cells(1, x) = "Pays" Then Exit For
      Next x
      For y = 1 To 15
          If sheet.Cells(1, y) = "GGID" Then Exit For
      Next y
  
      z = sheet.range("A1").CurrentRegion.Rows.Count
      sheet.Activate      'AST: if you remove this, next line throws an error
  
      Do
  
          If Not IsError(Application.Match(CLng(ggid), _
                         sheet.range(Cells(loopback, y), Cells(z, y)), 0)) Then
  
              adresik = Application.Match(ggid, _
                        sheet.range(Cells(loopback, y), Cells(z, y)), 0)
  
              If Cells(adresik + loopback, x) = country Then
                  Set rangefinder2 = Cells(adresik + loopback, y)
                  Exit Function
              End If
          Else
              Set rangefinder2 = Nothing
              Exit Function
          End If
  
          If loopback = 1 Then
              loopback = adresik
          Else
              loopback = loopback + adresik
          End If
      Loop
  
  End Function
  </code></pre>
  
  <p>Comment: the sheet is passed correctly (I checked with sheet.name), values x, y and z are calculated correctly.  The match finds first four rows (67 -71) but then fails to find the last one (1600). Value z(representing end of range) is 1601. I don't get it :/ Your help will be appreciated.</p>
  
  <p>So I made a test array to illustrate this easier:</p>
  
  <p>GGID    |s Pays 
  499455  |s Djibouti 
  499455  |s Italie
  13  |s Pologne
  499455  |s Afrique du Sud
  499455  |s Afrique du Sud</p>