title: Error handling VBA index match
body: <p>I'm building a template to let the user to view and edit some values horizontally according to dates, and then update those values in another sheet where the data is vertically organized. I'm using index/match function to find the value, then checking if there's any changes. </p>
  
  <p>After running this code, Column L is always #N/A. When I use index and match without VBA, some of the cell will be #N/A, because the date range is different and it cannot find a value. This is expected. If the lookUpvalue cannot be found or is the same as the original value, it should be kept the same, only making changes when value is found and different.</p>
  
  <pre><code>Public Sub TPHupdate()
  
  Dim i As Double
  Dim lookUpvalue As Variant
  Dim lastRow As Double
  Dim endRow As Double
  
  
  
  lastRow = Worksheets("TPHPivot").UsedRange.Rows.Count
  endRow = Worksheets("RoutingSpeed_MP").Range("G" &amp; Rows.Count).End(xlUp).Row
  
  For i = 2 To endRow
  On Error Resume Next
  
  lookUpvalue = Application.Index(Worksheets("TPHPivot").Range("E1001:P" &amp; lastRow), Application.Match(Worksheets("TPHEdit").Range("B" &amp; i), Worksheets("TPHPivot").Range("D1001:D" &amp; lastRow), 0), Application.Match(Worksheets("TPHEdit").Range("F" &amp; i), Worksheets("TPHPivot").Range("E1000:P1000"), 0))
  
  
  
  If lookUpvalue = CVErr(xlErrNA) Then
       Worksheets("RoutingSpeed_MP").Range("L" &amp; i).Value = "#N/A"
  
  ElseIf Worksheets("RoutingSpeed_MP").Range("G" &amp; i).Value &lt;&gt; lookUpvalue Then
      Worksheets("RoutingSpeed_MP").Range("L" &amp; i).Value = lookUpvalue
  
  End If
  
  Next i
  
  End Sub
  </code></pre>