title: VBA Function: Faster Way to Index Match
body: <p>I have a function which I use to do a two-column lookup and return a third value. I however need to call this function frequently within a macro to return the corresponding value and it dramatically increases execution time. Is there a faster way to lookup values?</p>
  <pre><code>Function FindValue(rng1 As Variant, rng2 As Variant) As Variant
  Dim varVal1 As Variant
  Dim varVal2 As Variant
  Dim rngTargetA As Range
  Dim rngTargetB As Range
  Dim lngRowCounter As Long
  Dim ws As Worksheet
  Dim wb As Workbook
  
  varVal1 = rng1
  varVal2 = rng2
  
  Set wb = ActiveWorkbook
  Set ws = wb.Sheets(&quot;TargetSheet&quot;)
  lngRowCounter = 2
  Set rngTargetA = ws.Range(&quot;A&quot; &amp; lngRowCounter)
  Set rngTargetB = ws.Range(&quot;B&quot; &amp; lngRowCounter)
  Do While Not IsEmpty(rngTargetA.Value)
      If rngTargetA.Value = varVal1 And rngTargetB.Value = varVal2 Then
          FindValue = ws.Range(&quot;C&quot; &amp; lngRowCounter).Value
          Exit Function
      End If
  
      lngRowCounter = lngRowCounter + 1
      Set rngTargetA = ws.Range(&quot;A&quot; &amp; lngRowCounter)
      Set rngTargetB = ws.Range(&quot;B&quot; &amp; lngRowCounter)
  Loop
  
  ' if we don't find anything, return an empty string '
  FindValue = &quot;Nothing&quot;
  
  
  End Function
  </code></pre>
  <p>Sample data i'm working with:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>DATE</th>
  <th>CURRENCY</th>
  <th>DAY TYPE</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1/1/2022</td>
  <td>TWD</td>
  <td>New   Years Day</td>
  </tr>
  <tr>
  <td>1/1/2022</td>
  <td>USD</td>
  <td>New   Years Day</td>
  </tr>
  <tr>
  <td>1/1/2022</td>
  <td>VND</td>
  <td>New Years Day</td>
  </tr>
  <tr>
  <td>1/1/2022</td>
  <td>ZAR</td>
  <td>New   Years Day</td>
  </tr>
  <tr>
  <td>1/2/2022</td>
  <td>ARS</td>
  <td>Weekend</td>
  </tr>
  <tr>
  <td>1/2/2022</td>
  <td>AUD</td>
  <td>Weekend</td>
  </tr>
  <tr>
  <td>1/2/2022</td>
  <td>BRL</td>
  <td>Weekend</td>
  </tr>
  <tr>
  <td>1/2/2022</td>
  <td>CAD</td>
  <td>Weekend</td>
  </tr>
  <tr>
  <td>1/2/2022</td>
  <td>CHF</td>
  <td>Berchtoldstag</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My attempt with results for a table with 10'000 rows:
  Is there a faster way yet?</p>
  <pre><code>Weekend
  This code ran successfully in 9.99999977648258E-03 seconds
  Weekend
  This code ran successfully in 0.129999995231628 seconds
  </code></pre>
  <pre><code>Sub RunFunction()
  
  Dim StartTime, SecondsElapsed As Double
  Dim FccstCCY As Variant
  Dim DateLoop As Variant
  
  Set wb = ActiveWorkbook
  Set ws = wb.Sheets(&quot;Sheet1&quot;)
  
  FccstCCY = &quot;ARS&quot;
  DateLoop = #12/31/2022#
  
  StartTime = Timer
  Debug.Print (FindValue(DateLoop, FccstCCY))
  SecondsElapsed = Round(Timer - StartTime, 2)
  Debug.Print (&quot;This code ran successfully in &quot; &amp; SecondsElapsed &amp; &quot; seconds&quot;)
  
  StartTime = Timer
  Debug.Print (FindValue2(DateLoop, FccstCCY))
  SecondsElapsed = Round(Timer - StartTime, 2)
  Debug.Print (&quot;This code ran successfully in &quot; &amp; SecondsElapsed &amp; &quot; seconds&quot;)
  
  End Sub
  
  Function FindValue(DateLoop As Variant, CCY As Variant) As Variant
  
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim LastRow, LastColumn, R, C As Long
  Dim StartCell As Range
  
  
  Dim Arr() As Variant ' declare an unallocated array.
  Set wb = ActiveWorkbook
  Set ws = wb.Sheets(&quot;Sheet1&quot;)
  Set StartCell = Range(&quot;A1&quot;)
  
  LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column
  
  Arr = ws.Range(StartCell, ws.Cells(LastRow, LastColumn))
  
  
  For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
      For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
          If Arr(R, 1) = DateLoop And Arr(R, 2) = CCY Then
              FindValue = Arr(R, 3)
          Exit Function
          End If
      Next C
  Next R
  
  End Function
  
  Function FindValue2(rng1 As Variant, rng2 As Variant) As Variant
  Dim varVal1 As Variant
  Dim varVal2 As Variant
  Dim rngTargetA As Range
  Dim rngTargetB As Range
  Dim lngRowCounter As Long
  Dim ws As Worksheet
  Dim wb As Workbook
  
  varVal1 = rng1
  varVal2 = rng2
  
  Set wb = ActiveWorkbook
  Set ws = wb.Sheets(&quot;Sheet1&quot;)
  lngRowCounter = 2
  Set rngTargetA = ws.Range(&quot;A&quot; &amp; lngRowCounter)
  Set rngTargetB = ws.Range(&quot;B&quot; &amp; lngRowCounter)
  Do While Not IsEmpty(rngTargetA.Value)
      If rngTargetA.Value = varVal1 And rngTargetB.Value = varVal2 Then
          FindValue2 = ws.Range(&quot;C&quot; &amp; lngRowCounter).Value
          Exit Function
      End If
  
      lngRowCounter = lngRowCounter + 1
      Set rngTargetA = ws.Range(&quot;A&quot; &amp; lngRowCounter)
      Set rngTargetB = ws.Range(&quot;B&quot; &amp; lngRowCounter)
  Loop
  
  ' if we don't find anything, return an empty string '
  FindValue2 = &quot;Nothing&quot;
  
  
  End Function
  
  </code></pre>