INDEX and MATCH with CHILDREN across two sheets.

AlexP

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.

Mark Cronk

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 Index-Match or VLOOKUP.

Help?

Mark

AlexP

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.

Mark Cronk

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 Index-Match or VLOOKUP.

Help?

Mark

AlexP

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.

AlexP

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.

Mark Cronk

Hi Alex, You found a solution. Perfect. Thank you for using the Community.

Mark