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
-
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.
-
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 :
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 Thank you so much for the detailed response! Your formula logic makes sense but I'm getting an INVALID REF error.
-
@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!
-
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
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"
-
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 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), "")
-
You are welcome and I will be happy to help you any time.
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"
-
Please help the Community by marking it as an ( Accepted Answer)
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"
-
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.
-
Please help the Community by marking it as an ( Accepted Answer)
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!