title: INDEX MATCH VBA (return a value based on two criteria)
body: <p>I'm doing some work for a client where they receive a weekly report of customers and regions (two columns, A for Customer &amp; B for Region, in a worksheet). * <em>I've omitted the other columns in this spreadsheet for the sake of clarity</em> </p>
  
  <p>They need to match the price on this spreadsheet with a reference spreadsheet, which we'll call Prices. Each customer and region combination yields a certain price which is stored on the aforementioned reference spreadsheet (In this reference worksheet [Prices], Column A is the customer, Column B is the region, Column C is the price for A customer in B region).  </p>
  
  <p>What I'm trying to do is:</p>
  
  <p>Based on A1(customer) and B1(region), C1 should be (lookupvalue for price on the reference worksheet).</p>
  
  <p>The traditional vlookup function would not work because multiple criteria need to be satisfied (i.e. we may have different prices for the same customer in a different region).</p>
  
  <p>I've tried the below with no success</p>
  
  <pre><code>Dim ULastRow As Long
  ActiveWorkbook.Worksheets(ActiveSheet.Name).UsedRange
  ULastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
  
  Range("C2").FormulaR1C1 = _
  "=Index(Range("C:C"), Match(Range("Prices!C1"), Range("Prices!C2:B34"), 0), 0)"
  Selection.AutoFill Destination:=Range("C2:C2" &amp; ULastRow)
  </code></pre>
  
  <p>See the below links for data examples (Unfortunately I am unable to add them in-line:</p>
  
  <p>What the customer gets: <a href="https://copy.com/EBABGl2EFjyFWcIH" rel="nofollow">https://copy.com/EBABGl2EFjyFWcIH</a></p>
  
  <p>The "Prices" or Reference data <a href="https://copy.com/oSSJuu7Dk4ox84tz" rel="nofollow">https://copy.com/oSSJuu7Dk4ox84tz</a></p>
  
  <p>Any advice would be greatly appreciated.</p>
  
  <p>Thanks in advance!</p>