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

Cinda.qCinda.q ✭✭✭✭✭
edited 08/26/21 in Best Practice
06/15/20 Edited 08/26/21
Accepted

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?

Best Answer

Answers

  • Cinda.qCinda.q ✭✭✭✭✭

    Thanks Paul!

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

  • StefanStefan ✭✭✭

    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.

  • StefanStefan ✭✭✭

    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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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:


  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    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!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @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.

  • Jon FriendJon Friend ✭✭✭✭✭

    this just saved me another 100 years of fuss. Brilliant. Thanks for all your help @Paul Newcome et al.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Jon Friend Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.