Index/Match Function Lookup Array with multiple criteria

Question

I looked but could not find a valid response for this specific circumstance.

I need to write a macro that searches a table for a specific Name (Column A), Material (Column B), and Color (Column C) combination then returns the price from Column D.

I can enter in the formula (array)

=INDEX(Sht1!A1:D5552,MATCH(1,(Sht1!A1:A5552=A1)*(Sht1!B1:B5552=B1)*(Sht1!G1:G5552=C1),0),4)

Where A1 has the Name of the item I am searching for, B1 has the Material and C1 has the Color and the value returns just fine. However, I have set it up to quickly choose between Name/Material/Color and I want to be able to pull the prices quickly so I am trying to write a Macro to run when I click a button.

The problem I am encountering is transposing the MATCH(1,(Sht1!A1:A5552=A1)(Sht1!B1:B5552=B1)(Sht1!C1:C5552=C1),0) part into VBA. I am trying to avoid just using the record function because it spits a .ForumlaArray out in R1C1 format that is difficult to interpret and update in the future.

I have tried

Application.Index(Sht1.Range("A1:D5552"), _
  Application.Match(1, Sht1.Range("A1:A5552") = Range("A1") & _
  "*" & Sht1.Range("B1:B5552") = Range("B1") & "*" & Sht1.Range("C1:C5552") = _
  Range("C1"), 0), 4)

But get a Type Mismatch

I also tried putting in the .ForumlaArray but get an "unable to set FormulaArray property of the range class" error (because it needs R1C1 Format)

"=INDEX(Sht1!A1:D5552,MATCH(1,(Sht1!A1:A5552=A1)*(Sht1!B1:B5552=B1)*(Sht1!C1:C5552=C1),0),4)"

I have variables set up a little differently but I am trying to simplify these examples to make it easier to interpret.

I think that VBA is overkill for what you are trying to do here. Just to make sure: your intended solution is to be able to select the Name, Material, and Color, and have the Price returned when a button is clicked, correct? What is it about VBA you believe will make selecting these faster? — Rick supports Monica
I'm confused by how VBA would make this faster? Don't you just need to update the cells that hold the Name, Material, and Color being searched for? — Dave.Gugg
I have a list of 600+ names each with multiple Materials/Types (over 5000 possibilities). In addition I have 6 separate price lists. I am making a form with dependent dropdowns to easily locate the Name/Mat/Color followed by a button that will fill out the Specifications then list the price from each list for that item. I am setting it up so if I need the prices for 6 items from multiple lists I can easily select them and pull the prices. I am assuming VBA is the easiest method for doing this. — D_Zab

cheezsteak cheezsteak · Accepted Answer · 2014-08-08T20:13:21

You are incorrectly using the Application.Match function and some fundamentals of VBA. In short, the function does not work like the Formula does.

AFAIK Application.Match takes a value to look for, and a 1D range of cells to look for it and returns the index of the cell in the array. So it sounds like you will need to do it multiple times to for each column. Granted, I can't find any acceptable documentation on it.

A correct usage would be:

row_index = Application.Match(Sht1.Range("A1").Value, Sht1.Range("A1:A5552"), 0)
price_value = Sht1.Cells(row_index, "D").Value

But that will only return the first match, not all of them. VBA won't increase performance if all you are doing is using replicating the formula in VBA. Other posters are correct that a different approach might be necessary.

Some other fundamentals you are getting wrong mostly stem from you taking the formula string and trying to translate it directly into Application.Match which AFAIK you can't do.

You are trying to specify conditions of when you have a match like this:

Sht1.Range("A1:A5552") = Range("A1")

What you are really doing is trying to compare Ranges. VBA is trying to evaluate this as a boolean before passing it to Application.Match. This returns a Type Error because the ranges are of different dimensions.

In case you didn't already do this: Sht1 needs to be declared as a Worksheet object and set to ActiveWorkbook.Sheets("Sht1")

Dim Sht1 As WorkSheet
Set Sht1 = ActiveWorkbook.Sheets("Sht1")

you can't simply hope that the variable Sht1 already refers to the worksheet "Sht1".

Then you try to string these together like this.

Sht1.Range("A1:A5552") = Range("A1") & "*" & _
Sht1.Range("B1:B5552") = Range("B1") & "*" & _
Sht1.Range("C1:C5552") = Range("C1")

I think you understand that & is for string concatenation but you are trying to concatenate, the strings "*" with the booleans/ranges which is undefined.

A VBA Solution

If you want a solution using VBA, you can either walk through all rows until you find a match:

Function FindProduct(name As String, material As String, color As String) As String

    Dim product_sheet As WorkSheet
    Set product_sheet = GetProductSheet() ' Workbooks(BOOK_NAME).Sheets(SHEET_NAME)

    Dim row_index as Long
    For row_index = 2 To product_sheet.UsedRange.Rows.Count

        If product_sheet.Cells(row_index, NAME_COL).Value = name _
        And product_sheet.Cells(row_index, MATERIAL_COL).Value = material _
        And product_sheet.Cells(row_index, COLOR_COL).Value = color Then

            FindProduct = product_sheet.Cells(row_index, PRODUCT_COL).Value
            Exit Function

        End If

    Next row_index

End Function

Or if performance is an issue:

Keep your data sorted on columns "A", "B", "C" (for this example, in that order.)
Find the range of rows that match in column "A"
From that range, find the sub range of rows that match in column "B"
From that range, return the sub range of rows that match in column "C"

I may provide that later.

Index/Match Function Lookup Array with multiple criteria

2 Answers

A VBA Solution