INDEX and MATCH with CHILDREN across two sheets.
Hi All,
I'm having some trouble using INDEX and MATCH together to pull data from cells on one sheet across to another. I'm making an assumption that this is the best method however if I'm mistaken, please advise.
I have a situation similar to the below where "MainName" is a Project Name and data beneath it is it's child. '#' is simply a number of some kind. This kind of setup is then replicated several times as there are several projects.
Thus selecting the data (#) based on "MainName" and A,B,C...and so on is my goal here.
Column 5 # is what I am interested in getting across to another sheet. Lets call this Sheet 1.
The other sheet basically has columns MainName,A,B,C..and so on, and would ideally pull in the # based on "MainName" and A,B,C... (see below) Rows are added from an automated workflow and so want to pick up the data automatically without having to manually reference it each time a new project is populated.
I think I need to something like the following ( I will use B as my example):
=INDEX({Column5},MATCH({Sheet 1 B},{Sheet 1 range  Column 4}))
I would expect this to return the value # from column 5 in row containing B. However I don't think it does (maybe because of the parent  child relationship?  not sure).
Furthermore, this does not take into account the "MainName"  meaning if I have more than one, the formula wont grab the correct data.
I hope this makes sense however if clarification needed please let me know.
Any assistance on this one would be greatly appreciated.
Thank you.
Best Answers

Hi @AlexP ,
I think to get what you're looking for you have to create a helper column that combines the parent name with the child and gives you a unique value in each row. A formula to to do that would be:
=Parent([Primary Column]@row)+""+[Primary Column]@row
Once each row has a unique value you can use if for IndexMatch or VLOOKUP.
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Hi everyone,
Turns out I was really trying to over complicate the situation  after a step back I just decided to perform the calculation in the sheet that I wanted it in, rather than doing it elsewhere and then trying to find it.
Again @Mark Cronk I think your solution is probably the only feasible one  it just doesn't work so well in my particular situation.
Thanks.
Answers

Hi @AlexP ,
I think to get what you're looking for you have to create a helper column that combines the parent name with the child and gives you a unique value in each row. A formula to to do that would be:
=Parent([Primary Column]@row)+""+[Primary Column]@row
Once each row has a unique value you can use if for IndexMatch or VLOOKUP.
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Hi @Mark Cronk
Thanks for your suggestion. I don't think this will be an option for me as concatenating the strings will result in very long strings which I would then have to filter somehow to get the number I want at the end.
My only other thought is (and I don't know whether Smartsheet allows for this) is try to find a cell relative to another position.
In my case, The layout would always be the same, so if I can look for one cell in particular and then shift the search for example 3 columns right and 3 rows down  then the cell in question would be found  again no idea if this is possible.
Not sure if there are any other suggestions, or if need to come up with a better sheet layout.
Thanks
Leaving this open to further suggestion.

Hi everyone,
Turns out I was really trying to over complicate the situation  after a step back I just decided to perform the calculation in the sheet that I wanted it in, rather than doing it elsewhere and then trying to find it.
Again @Mark Cronk I think your solution is probably the only feasible one  it just doesn't work so well in my particular situation.
Thanks.

Hi Alex, You found a solution. Perfect. Thank you for using the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.