Excel VBA worksheetfunction Index Match with an Array

Question

Is there a way to get VBA to except Array formulas with INDEX MATCH when using a .worksheetfunction?

My first formula works since it's not an array I presume?

This code works

Dim VType As string
VType = Application.WorksheetFunction.Index(Sheets(sheetname).Range("$B:$B"), Application.WorksheetFunction.Match("*" & VendorCode & "*", Sheets(sheetname).Range("$A:$A"), 0), 1)

But then when I add a 2nd match I get an arror: Type mismatch

Dim RetORWaste As String
RetORWaste = Application.WorksheetFunction.Index(Sheets(wsMaster.Name).Range("$F:$F"), Application.WorksheetFunction.Match(("*" & VendorCode & "*") & ("*" & VRegion & "*"), (Sheets(wsMaster.Name).Range("$B:$B")) & (Sheets(wsMaster.Name).Range("$C:$C")), 0), 1)

Both sheetname and wsMaster.name are string. The wsMaster.Name also gets the correct sheetname. So it must be the array?

(Sheets(wsMaster.Name).Range("$B:$B")) & (Sheets(wsMaster.Name).Range("$C:$C")) is trying to concatenate two arrays... which you can't do and will throw a type mismatch. You could use Evaluate here. — BigBen
– BigBen, Commented May 5, 2020 at 19:14
@BigBen I've seen a few posts online where people use evaluate but not sure where in the formula to add it in my case? — Simone Evans
– Simone Evans, Commented May 5, 2020 at 19:22
You just RetORWaste = wsMaster.Evaluate("yourformulaasastringminustheleadingequals") — BigBen
– BigBen, Commented May 5, 2020 at 19:26
@FaneDuru - perhaps... but I'm trying to "teach a man to fish." (Give a man a fish, and you'll feed him for a day. Teach a man to fish, and you've fed him for a lifetime.") haha. — BigBen
– BigBen, Commented May 5, 2020 at 20:32
You're doing too much in one line - break it up into multiple lines and use some variables. — Tim Williams
– Tim Williams, Commented May 5, 2020 at 20:35

Tim Williams · Accepted Answer · 2020-05-05 20:54:44Z

2

If I'm understanding correctly that you want to do a "two column match" as here: https://www.excel-easy.com/examples/two-column-lookup.html

Sub tester()

    Dim RetORWaste As String, wsMaster As Worksheet, m, f
    Dim VendorCode, VRegion

    Set wsMaster = ActiveSheet

    VendorCode = "A"
    VRegion = "B"

    f = "=MATCH(""*{vend}*""&""*{region}*"",B:B&C:C,0)"
    f = Replace(f, "{vend}", VendorCode)
    f = Replace(f, "{region}", VRegion)

    m = wsMaster.Evaluate(f) '<<do not use Application.Evaluate here, or the
                             '  formula will evaluate in the context of the 
                             '  active sheet, which might not be wsMaster

    If Not IsError(m) Then
        'got a match so get the value from col F
        RetORWaste = wsMaster.Cells(m, "F")
        Debug.Print RetORWaste
    End If

End Sub

edited May 5, 2020 at 20:54

answered May 5, 2020 at 20:49

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Simone Evans Over a year ago

It works!! Thank you very much. I did try splitting the formula before, using replace. But I was either doing it wrong, or the array was causing problems. I'm still learning a lot about VBA code... Thank you very much!

Tim Williams Over a year ago

No problem. FYI I don't think you can do this particular lookup using Application.Match - you need to use the Evaluate method.

Simone Evans Over a year ago

From all the posts I read, evaluate always seemed to be the solution for array formulas. But when the variables and multiple formulas get combined I guess it gets a bit tricky.

Tim Williams Over a year ago

The issue with this particular case is the & can't be applied when using WorksheetFunction.Match - it only works in a formula on the worksheet.

Collectives™ on Stack Overflow

Excel VBA worksheetfunction Index Match with an Array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related