Index Set Up

Laura Krylov

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!

Andrée Starå

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, [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!

Bassam Khalil

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 :

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.

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

@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: [email protected]

Andrée Starå

@Laura Krylov

No worries!

I'm always happy to help!

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

@Laura Krylov

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

Bassam Khalil

@Laura Krylov 

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

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.

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

@Laura Krylov 

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