Index Match using VBA Function in Microsoft Excel

Question

I'm new to Excel VBA and am trying to write a function that lets me use INDEX, MATCH and COUNTIFS functions to find value for a given column by matching criteria in other columns (multiple).

I have a table named Price which contains some prices offered in different locations based on an assigned category:

Area    Category    Cost    Retail Price    Wholesale Price
USA Bad 1   13  25
Canada  Okay    2   14  26
Mexico  Good    3   15  27
USA Excellent   4   16  28
Canada  Bad 5   17  29
Mexico  Okay    6   18  30
USA Good    7   19  31
Canada  Excellent   8   20  32
Mexico  Bad 9   21  33
USA Okay    10  22  34
Canada  Good    11  23  35
Mexico  Excellent   12  24  36

In Excel I can use the an array formula to get this (see video example here). The below formula lets me get Wholesale Price for Okay category in Mexico

{=INDEX(Price,MATCH(1,COUNTIFS(L12,Price[Area],M12,Price[Category]),0),MATCH(N12,Price[#Headers],0))}

Where

L12 = Mexico
M12 = Okay 
N12 = Wholesale Price 

I'd like to make a VBA function ProdPrice that can return this value without a messy-looking formula. Here's what I have so far:

Function ProdPrice(locs, cat, pricetype)
    
    'Get column number corresponding to selected "pricetype"
    col_num = WorksheetFunction.Match( _
                pricetype, _
                ActiveSheet.ListObjects("Price").HeaderRowRange.Select, _
                0)
                
    ProdPrice = WorksheetFunction.Index( _
                    ActiveSheet.ListObjects("Price"), _
                    WorksheetFunction.Match( _
                        1, _
                        WorksheetFunction.CountIfs( _
                            ActiveSheet.ListObjects("Price").ListColumns("Area").DataBodyRange.Select, _
                            locs, _
                            ActiveSheet.ListObjects("Price").ListColumns("Category").DataBodyRange.Select, _
                            cat), _
                        0), _
                    col_num)

End Function

Instead of a single call, I broke the function into two segments with _ for better readability.

When I run this I get a #VALUE! output.

Any suggestions on how I can go about this? Also from what I understand, the above function will look for a table called price in the worksheet where the function is called - I'd like for it to be able to look at price in the workbook instead.

Answer 1

I assume that your values are always in an Excel structured table

Remarks:

• Function is case insensitive
• Table headers and values don't contain | character
• Function will return first matching value (area and category)
• Columns order doesn't care

Please read code's comments and adjust it to fit your needs

Code

Public Function lookupPrice(ByVal table As Range, ByVal lookup_area As String, ByVal lookup_category As String, ByVal pricetype_header As String) As Variant
    
    ' Define column headers
    Dim areaHeader As String
    Dim categoryHeader As String
    areaHeader = "Area"
    categoryHeader = "Category"
    
    ' Get area column number from headers
    Dim areaColumn As Long
    areaColumn = Application.Match(areaHeader, table.ListObject.HeaderRowRange, False)
    
    ' Get category column number from headers
    Dim categoryColumn As Long
    categoryColumn = Application.Match(categoryHeader, table.ListObject.HeaderRowRange, False)
    
    ' Get price column number from headers according to function parameter
    Dim priceColumn As Long
    priceColumn = Application.Match(pricetype_header, table.ListObject.HeaderRowRange, False)
    
    
    ' Get area column values into 1d array
    Dim areaValues As Variant
    areaValues = WorksheetFunction.Transpose(Application.Index(table.Columns(areaColumn), 0, 1))
    
    ' Get category column values into 1d array
    Dim categoryValues As Variant
    categoryValues = WorksheetFunction.Transpose(Application.Index(table.Columns(categoryColumn), 0, 1))
    
    ' Define and redimension an array to hold the concatenated area and category values
    Dim areaCategoryValues() As Variant
    ReDim areaCategoryValues(1 To UBound(areaValues))
    
    ' Concatenate the area and category values and store them in an array
    Dim counter As Long
    For counter = 1 To UBound(areaValues)
        areaCategoryValues(counter) = areaValues(counter) & "|" & categoryValues(counter)
    Next counter
    
    ' Get the matching row according to lookup values
    Dim resultRow As Long
    resultRow = Application.Match(lookup_area & "|" & lookup_category, areaCategoryValues, False)
    
    ' Get the result value according to the price column number
    Dim result As Variant
    result = Application.Index(table.Columns(priceColumn), resultRow)
    
    ' Return the value
    lookupPrice = result
    
End Function

Let me know if it works