Index Set Up

I have two Sheets: P2P and LookupP2PWorkflow (two fields Plant and Workflow).

P2P contains columns called Location and a column called Workflow.

LookupP2PWorkflow contains only 2 columns Plant and Workflow.

P2P.Location = LookupP2PWorkflow.Plant

I would like to write a formula that references the Location column in P2P and goes to the LookupP2PWorkflow sheet to find its corresponding Workflow value. I"m trying to set up an Index. This is the formula I have that isn't working:

=INDEX(LookupP2PWorkflow:LookupP2PWorkflow,Location@row,2)

Any help is greatly appreciated!

Comments

  • Andrรฉe Starรฅ
    Andrรฉe Starรฅ Community Champion

    Hi @Laura Krylov

    I hope you're well and safe!

    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,ย andree@workbold.com)

    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 EXPERT CONSULTANT & PARTNER

    Andrรฉe Starรฅ | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Bassam Khalil
    Bassam Khalil โœญโœญโœญโœญโœญโœญ
    edited 06/09/21

    Hi @Laura Krylovย 

    Hope you are fine, please try the following formula:

    Workflow ( In PSP ) =INDEX({LookupP2PWorkflow Range 1}, MATCH(Location@row, {LookupP2PWorkflow Plant}), 2)

    1- {LookupP2PWorkflow Range 1} as following screenshot :

    1.jpg


    2- {LookupP2PWorkflow Plant} as following screenshot :

    2.jpg


    PMP Certified

    bassam.khalil2009@gmail.com

    โ˜‘๏ธ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Laura Krylov
    Laura Krylov โœญโœญ

    @Bassam Khalil Thank you so much for the detailed response! Your formula logic makes sense but I'm getting an INVALID REF error.

    image.png image.png


  • Laura Krylov
    Laura Krylov โœญโœญ

    @Andrรฉe Starรฅ Thank you so much for being willing to dig into this with me. I'm trying to work through the formula Bassam provided so I don't want to bother both of you.

    This community is incredible! People are so helpful!

  • Bassam Khalil
    Bassam Khalil โœญโœญโœญโœญโœญโœญ
    edited 06/09/21

    @Laura Krylov

    Am sure about the formula because i test it, but i think maybe there is a problem with column type or in reference, could you please share me on a copy of those sheets as an admin to create the exact formula for you (after removing or replacing any sensitive information)

    my Email: Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    โ˜‘๏ธ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrรฉe Starรฅ
    Andrรฉe Starรฅ Community Champion

    @Laura Krylov

    No worries!

    I'm always happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrรฉe Starรฅ | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Laura Krylov
    Laura Krylov โœญโœญ

    @Bassam Khalil Thank you for the modifications to the formula! It's working perfectly now. I really appreciate your help!

    Final Result for anyone using this thread as a reference:

    =IFERROR(INDEX(COLLECT({LookupP2PWorkflow-Workflow}, {LookupP2PWorkflow Plant}, Location@row), 1), "")

  • Bassam Khalil
    Bassam Khalil โœญโœญโœญโœญโœญโœญ

    @Laura Krylov

    You are welcome and I will be happy to help you any time.

    PMP Certified

    bassam.khalil2009@gmail.com

    โ˜‘๏ธ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil โœญโœญโœญโœญโœญโœญ
    edited 06/10/21

    @Laura Krylovย 

    Please help the Community by marking it as anย ( Accepted Answer)

    PMP Certified

    bassam.khalil2009@gmail.com

    โ˜‘๏ธ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Laura Krylov
    Laura Krylov โœญโœญ

    For anyone following this thread, you'll also need to make sure the references are set. When I recreated this in another sheet, I had to manually create the references.

    To do this, right click on the cell with the formula and choose Manage References.

    image.png


    Then, Create a new reference. Make sure the name of the reference is exactly the same as the name you put in the formula.

    image.png


  • Bassam Khalil
    Bassam Khalil โœญโœญโœญโœญโœญโœญ

    @Laura Krylovย 

    Please help the Community by marking it as anย ( Accepted Answer)

    PMP Certified

    bassam.khalil2009@gmail.com

    โ˜‘๏ธ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!