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

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?
0
Best Answer
-
Paul Newcome ✭✭✭✭✭
It is possible using an INDEX/COLLECT.
=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)
thinkspi.com
13
Answers
It is possible using an INDEX/COLLECT.
=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)
thinkspi.com
Thanks Paul!
I just came across this function and it tested out perfectly.
Hello Cinda,
yes you can. There is an excellent help article in the Smartsheet Learning Center:
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Happy to help! 👍️
thinkspi.com
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:
@Kerron Mitchell What exactly is in the {Completed} range?
thinkspi.com
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!
SMARTSHEET PARTNER & CONSULTANT / EXPERT
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
@Paul Newcome & @Andrée Starå
The formula I had actually worked! I don't know why it wasn't working until after a few refreshes.
Thank you though for your willingness to help me!
@Kerron Mitchell
Excellent!
Glad you got it working!
I'm always happy to help!
SMARTSHEET PARTNER & CONSULTANT / EXPERT
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
this just saved me another 100 years of fuss. Brilliant. Thanks for all your help @Paul Newcome et al.
@Jon Friend Happy to help. 👍️
thinkspi.com