Index Set Up

Options

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å ✭✭✭✭✭✭
    Options

    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
    Options

    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 :


    2- {LookupP2PWorkflow Plant} as following screenshot :


    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
    Options

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


  • Laura Krylov
    Options

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

    @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å ✭✭✭✭✭✭
    Options

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

    @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 ✭✭✭✭✭✭
    Options

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

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

    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.


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


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

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