INDEX MATCH VBA (return a value based on two criteria)

Question

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!

Answer 1

Imho this is a classic case of "use a database instead of Excel", especially if you want to make these kind of queries regularly.

However, something like this should achieve what you want in VBA:

Dim customer As String
Dim region As String
Dim price as Double

For r = 2 To ThisWorkbook.Worksheets("Client").UsedRange.Rows.Count

        customer = ThisWorkbook.Worksheets("Client").Cells(r, 1).Value
        region = ThisWorkbook.Worksheets("Client").Cells(r, 2).Value
        price = 0.00

    For s = 2 To ThisWorkbook.Worksheets("Prices").UsedRange.Rows.Count


        If ThisWorkbook.Worksheets("Prices").Cells(s, 1).Value = customer And ThisWorkbook.Worksheets("Prices").Cells(s, 2).Value = region Then

            price = ThisWorkbook.Worksheets("Prices").Cells(s, 3).Value
            Exit For

        End If

    Next

        If price = 0.00 Then
            ThisWorkbook.Worksheets("Client").Cells(r, 3).Value = "This customer/region combination is not stored in the reference sheet"
        Else
            ThisWorkbook.Worksheets("Client").Cells(r, 3).Value = price
        End If
Next

Change your worksheet and the starting row (r) accordingly