title: VBA multiple variable search
body: <p>I'm trying to search through a large table that has many cases and return a value for it [I've tried Index/Match and Vlookup, but since there are many different Market variables, I can't get it to work]</p>
  
  <p>Template Link <a href="https://drive.google.com/file/d/0B4sKjkAZVkt6YzVSUHFwQnRFVk0/view?usp=sharing" rel="nofollow noreferrer">enter link description here</a></p>
  
  <p>Here is the main-input Excel table. The important factors in this table are "Client ID, Currency, Market" (input by user).</p>
  
  <p><a href="https://i.stack.imgur.com/WhXLL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WhXLL.png" alt="enter image description here"></a></p>
  
  <p>Using these three variables, I want to search through this list, and return the MIN value in the main-input table. <strong>As you can see, there are certain cases where 2/3 of the variables are the same.</strong></p>
  
  <p><a href="https://i.stack.imgur.com/tiTCB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tiTCB.png" alt="enter image description here"></a></p>
  
  <pre><code>Option Explicit
  
  Sub komisijas_calc_Click()
  
  'Declare the variables
  Dim klienta_nr As Long
  Dim ISIN As String
  Dim Cena As Double
  Dim Skaits As Double
  Dim Komisija As Double
  Dim vk As String
  Dim Summa As Double
  Dim kSheet As Worksheet
  
  Dim lngFirstRow As Long: lngFirstRow = 2
  Dim lngLastRow  As Long
  Dim lngCol      As Long: lngCol = 2
  Dim lngCounter  As Long
  Dim lngKom As Long: lngKom = 11
  
  
  
  
  Set kSheet = ThisWorkbook.Sheets("spec_klienti")
  
  
  With Worksheets(1)
      lngLastRow = .Cells(.Rows.Count, lngCol).End(xlUp).Row
  
      For lngCounter = lngFirstRow To lngLastRow
  
      klienta_nr = Range("B" &amp; lngCounter).Value
      ISIN = Range("E" &amp; lngCounter).Value
      Cena = Range("H" &amp; lngCounter).Value
      Skaits = Range("I" &amp; lngCounter).Value
      vk = Range("D" &amp; lngCounter).Value
      Summa = Cena * Skaits
  
  
  Select Case klienta_nr
  
  
  
      Case 1
                  '(Vācija, Francija, Nīderlandes, Itālija, Īrija) - 30 EUR MIN
                  If klienta_nr = 1 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
                      Komisija = Summa * 0.01
                      .Cells(lngCounter, lngKom) = Komisija
                          End If
                  If klienta_nr = 636092 And Komisija &lt;= 30 Then
                      .Cells(lngCounter, lngKom) = 30
                          End If
  
  
                  If klienta_nr = 1 And (Left(ISIN, 2) &lt;&gt; "DE" Or Left(ISIN, 2) &lt;&gt; "FR" Or Left(ISIN, 2) &lt;&gt; "NL" Or Left(ISIN, 2) &lt;&gt; "IT" Or Left(ISIN, 2) &lt;&gt; "IE") Then
                      Komisija = Summa * 0.003
                      If Komisija &gt;= 40 Then
                          .Cells(lngCounter, lngKom) = 40
                              End If
                          End If
  
  
      Case 2
                  '(Vācija, Francija, Nīderlandes, Itālija, Īrija) - 30 EUR MIN
                  If klienta_nr = 2 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
                      Komisija = Summa * 0.01
                      .Cells(lngCounter, lngKom) = Komisija
                          End If
                  'Set 30 EUR Min
                  If klienta_nr = 2 And Komisija &lt;= 30 Then
                      .Cells(lngCounter, lngKom) = 30
                          End If
  
                  If klienta_nr = 2 And (Left(ISIN, 2) &lt;&gt; "DE" Or Left(ISIN, 2) &lt;&gt; "FR" Or Left(ISIN, 2) &lt;&gt; "NL" Or Left(ISIN, 2) &lt;&gt; "IT" Or Left(ISIN, 2) &lt;&gt; "IE") Then
                      GoTo AllElseFails
                          End If
  
  
  Case Else
  
       AllElseFails:
  
                'IP2, 0.03% komisija, 40 EUR/USD Max
                  If Right(vk, 1) = 1 Or Right(vk, 1) = 8 Then
                      Komisija = Summa * 0.003
                      .Cells(lngCounter, lngKom) = Komisija
                          End If
                'IP1, 0.1% komisija, 40 EUR/USD Max
                  If Right(vk, 1) = 7 Then
                      Komisija = Summa * 0.01
                      .Cells(lngCounter, lngKom) = Komisija
                          End If
                  'Komisija MAX is 40, so anything &gt;=40 equals 40
                  If Komisija &gt;= 40 Then
                      .Cells(lngCounter, lngKom) = 40
                          End If
              'End If
    End Select
   Next lngCounter
  End With
  End Sub
  </code></pre>