title: Vlookup using python when data given in range
body: <p>I have two excel files, I want to perform vlookup and find difference of costs using python or even excel.</p>
  <p>My files look like this</p>
  <p><strong>source_data.xlsx</strong> contains contains distance covered and their price, example distance range from 1 to 100 should be charged 4800 and distance range from 101 to 120 should be charged 5100.</p>
  <pre><code>DISTANCE     COST
  
  1-100        4800
  
  101-120      5100
  
  121-140      5500
  
  141-160      5900
  
  161-180      6200
  
  181-200      6600
  
  210-220      6900
  
  221-240      7200
  </code></pre>
  <p><strong>Analysis.xlsx</strong></p>
  <pre><code>loading_station  distance_travel     total_cost    status
  
  PUGU                  40                4000       PAID
  
  
  PUGU                  80                3200       PAID
  
  MOROGORO              50                5000       PAID
  
  MOROGORO              220               30400      PAID
  
  DODOMA                150               5100       PAID
  
  KIGOMA                90                2345       PAID
  
  DODOMA                230               6000       PAID
  
  DODOMA                180               16500      PAID
  
  KIGOMA                32                3000       PAID
  
  DODOMA                45                6000       PAID
  
  DODOMA                65                5000       PAID
  
  KIGOMA                77                1000       PAID
  
  KIGOMA                90                4000       PAID
  </code></pre>
  <p>Actual Cost for distance is given in <code>source_data.xlsx</code>, I want to check cost in <code>Analysis.xlsx</code> if it correspond to Actual value, I want to detect underpayment and overpayment.</p>
  <p>Desired Output should be like this, with two column added, <code>source_cost</code> which is taken from <code>source_xlsx</code> by using <code>vlookup</code> and difference which is difference between <code>total_cost</code> and <code>source_cost</code></p>
  <pre><code>loading_station distance_travel total_cost  status  source_cost Difference
  
  PUGU               40                4000     PAID     4800        -800
  
  PUGU               80                3200     PAID     4800        -1600
  
  MOROGORO           50                5000     PAID     4800         200
  
  MOROGORO           220               30400    PAID     6900         23500
  
  DODOMA             150               5100     PAID     5900         -800
  
  KIGOMA             90                2345     PAID     4800         -2455
  
  DODOMA             230               6000     PAID     7200         -1200
  
  DODOMA             180               16500    PAID     6200          10300
  
  KIGOMA             32                3000     PAID     4800          -1800
  
  DODOMA             45                6000     PAID     4800           1200
  
  DODOMA             65                5000     PAID     4800           200
  
  KIGOMA             77                1000     PAID     4800           -3800
  
  KIGOMA             90                4000     PAID     4800           -800
  </code></pre>
  <p>My code so far</p>
  <pre><code># import pandas
  import pandas as pd
  
  # read excel data
  source_data = pd.read_excel('source_data.xlsx')
  analysis_file = pd.read_excel('analysis.xlsx')
  source_data.head(5)
  analysis_file.head(5)
  </code></pre>