Index / Match in VBA, works fine until I added the second Match function in.

Index / Match in VBA, works fine until I added the second Match  function in.

What I'm trying to do is get the index result depending on whether two other columns in the table have certain data in them.

So the Row part of the index function (the match functions) is running on two criteria. Works with just one no problems, and I've tried it with both Match functions in it alone, and it works with either one perfectly. just gives me a type mismatch when they are both in.

The data does exist, sTitle is populated from a  drop down select and the drop read from table range its running the match in, and sName also populates from a drop down that populates based on what is listed as in same rows as the project picked (sTitle)

sDate = Application.Index(Range("Projects[Start Date]"), Application.Match(sTitle, Range("Projects[Title]"), 0), Application.Match(sName, Range("Projects[Resource]"), 0))

Open in new window

LVL 2
Conor_NewmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you post your sample file..As in order to troubleshoot the formula need to look in what you are doing which we can correct in order for this to work..

Saurabh...
NorieAnalyst Assistant Commented:
Try splitting things up a little.
Dim Res1 As Variant
Dim Res2 As Variant

    Res1 = Application.Match(sTitle, Range("Projects[Title]"), 0)
    Res2 = Application.Match(sName, Range("Projects[Resource]"), 0)

    If Not IsError(Res1) And Not IsError(Res2) Then
        sDate = Application.Index(Range("Projects[Start Date]"), Res1, Res2)
    Else
        MsgBox "No match found"
    End If

Open in new window

Now that isn't a solution but it should help avoid errors and with debugging.
Conor_NewmanAuthor Commented:
No idea why splitting it up worked, but it did. Thank you!

Still bugging me why it couldn't do it within one line. But, it works now, that's what matters most!


Thanks a million!
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Conor_NewmanAuthor Commented:
Damnit.

Ok, it didn't work, it worked for the first 3 selections then bugged again.

But it did allow me to narrow down the issue I think. It looks like its because in those two ranges for the match criteria, there are multiple instances for both sTitle and sName, the index match is finding the first occurrence in each lookup.

So the formula can end up as:

sDate = Application.Index(Range("Projects[Start Date]"), 3, 26)

Open in new window


Hence the error.  I need it to find the row where both those values are present on the one line.
Perhaps an index match formulae is not the best way of doing that after all.
Conor_NewmanAuthor Commented:
Sauraph,

the workbook contains company sensitive information, if I have to I'll duplicate it, and populate with dummy info, but it would be a big job to do.
NorieAnalyst Assistant Commented:
Conor

As I indicated what I posted wasn't really a solution, but it could assist in finding what the problem is.
NorieAnalyst Assistant Commented:
Conor

Are you using both MATCH formulas to return a row, rather than row and column?
Conor_NewmanAuthor Commented:
Yes, the column I want the info from in the Index part, I need it to return that value, where the values in the two other columns met certain criteria.

|Title|Start Date    |Resource
|A      |20/10/2015 |Jane Doe
|B      |21/11/2015 |A.N. Other
|C       |01/08/2015|Jane Doe
|D      |03/02/2016 |A.N. Other
|B       |15/03/2016 |John Smith

So if that was the table, and I selected from my form, Title: B, Resource: John Smith
I'd want the 15/03/2016 to be returned, the final entry there, as its the line where both criteria are met.

That make sense?
NorieAnalyst Assistant Commented:
Perhaps something like this, which works with your sample data.
 Res = Evaluate("MATCH(""" & stitle & sname & """, Projects[Title]&Projects[Resource],0)")
    
    If Not IsError(Res) Then
        sDate = Range("Projects[Start Date]")(Res)
    End If

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Conor_NewmanAuthor Commented:
That looks good. Not going top jump the gun this time though ;-) Going to test it a few times, and be sure, ;-) Will comment back in 5 min and let you know.
Conor_NewmanAuthor Commented:
OK, with 20 different selection, that worked. I think that's the solution I was looking for. Thank you so much for your time. I really appreciate it!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.