Can you use 2 match criterias in an index/match formula?

Cinda.q · 06/15/20

I am looking to structure an index and match formula, but it needs to match two columns of data and having some troubles. Is this possible or will I need to create a work-around with a Join function in another column?

Paul Newcome · 06/16/20

It is possible using an INDEX/COLLECT.

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

Paul Newcome · 06/16/20

It is possible using an INDEX/COLLECT.

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

Cinda.q · 06/16/20

Thanks Paul!

I just came across this function and it tested out perfectly.

Stefan · 06/16/20

Hello Cinda,

yes you can. There is an excellent help article in the Smartsheet Learning Center:

https://www.smartsheet.com/advanced-vlookup-multiple-criteria#how-to-use-index-match-with-multiple-criteria

Hope this helps

Stefan

Stefan · 06/16/20

Even though the title of the link mentions VLOOKUP only, there is much to read about usage of the INDEX + MATCH combination. Helped me, when I started using it.

Greetings

Stefan

Paul Newcome · 06/16/20

Happy to help! 👍️

Kerron Mitchell · 08/18/21

Hello!

I have been working on a similar issue/formula and am looking for help.

As students take e-Learnings, we would like to track their progress automatically rather than having to sift through their completions. I have 2 sheets set up:

1. A Sheet where a form is submitted once the student completes the e-Learning. This includes both the student's name and the name of the e-Learning.

2: The tracker for admin to see the progress of the students. It is on this form that I would like to have the boxes automatically checked if the student's name and the name of the e-Learning completed matches.

If anyone thinks of a more efficient way to do this, I am up for suggestions.

The formula I currently have is:

=INDEX(COLLECT({Completed}, {Tracker Name}, [email protected], {eLearning Name}, "Gaining Ground"), 1)

It is showing an error.

Thank you,

This is Sheet 1

Here is the tracker:

Paul Newcome · 08/18/21

@Kerron Mitchell What exactly is in the {Completed} range?

Andrée Starå · 08/18/21

Hi @Kerron Mitchell

I hope you're well and safe!

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!