Index/Match Cross-Sheet Formula Help Request

Deanna Vandermeer

I am stuck on what probably is a fairly simple cross-sheet INDEX/MATCH formula but this evening, for the life of me, I can't seem to crack it. It may be that I need to sleep on it, or it might be that in a few days I will be leaving on vacation for two weeks and need to get this complete and over to my supervisor before I leave. Either way, I am reaching out to the wonderful Smartsheet Community members for assistance.

I have one data sheet being fed data via an intake form where we are collecting columns and columns and rows and rows of data for each building in our district. What I need to put together is a Master Roll Up sheet that I have already set up that will show this data in a more readable format for the supervisor (and yes, I've tried reports already but they won't work in this instance). I need to bring over the building's Interventionist's name (of which they could have up to five people), match the building name and enter the value into the row so that each row pulls only over one name in each cell per row. Simple, right. Yeah, I am stumped and probably very tired. I have already built out the master sheet with a parent/child hierarchy and each parent row contains the building name and Principal's name, each child row is where I need to pull over the Interventionist's name (and then ultimately pull in their other info). The intake sheet is broken out with columns to correspond to either I1, I2, I3, I4 or I5 and their data (name, position, content area, etc.).

Anyway, I am hopeful that someone is able to help me. Thank you.

Bassam Khalil

Hi @Deanna Vandermeer

Hope you are fine, to give you quick answer and avoid losing your time in long discussions and questions, please share me on a copy of your sheets as an admin after removing any sensitive data just keep a sample data and I will try to create this formula for you to let you go to enjoy your vacation today :).

Andrée Starå

Hi @Deanna Vandermeer

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

I hope that helps!

Be safe and have a fantastic weekend!

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!