I'm doing some work for a client where they receive a weekly report of customers and regions (two columns, A for Customer & B for Region, in a worksheet). * I've omitted the other columns in this spreadsheet for the sake of clarity
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).
What I'm trying to do is:
Based on A1(customer) and B1(region), C1 should be (lookupvalue for price on the reference worksheet).
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).
I've tried the below with no success
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" & ULastRow)
See the below links for data examples (Unfortunately I am unable to add them in-line:
What the customer gets: https://copy.com/EBABGl2EFjyFWcIH
The "Prices" or Reference data https://copy.com/oSSJuu7Dk4ox84tz
Any advice would be greatly appreciated.
Thanks in advance!