Using CHILDREN for ranges in VLOOKUP or INDEX MATCH

weswillis

Hoping someone can help with this as it seems possible... I have a sheet for contacts at individual centers across a state. There are roles assigned to the contacts, so for example there may be a director, a supervisor and a coordinator.

In the sheet, each parent row is a row with general center information and (the part I'm having trouble with) a roll-up of contact information from certain child rows. The rows with individual contact information are added under as children. I'm attempting to look in the child rows to find the row for the role of, say, director and then return the column on that row containing their name. In the end I can export a report of just the parent rows (to do some further office automation/mail-merging).

My best estimation is that this can be accomplished by either the VLOOKUP or INDEX/MATCH (or maybe COLLECT?). When I attempt to define my ranges using children, the formula will return unparsable.

Additional information: The intent with updates will be to collect form responses into this sheet. Staff will get notified and the update is moved to the correct center/parent row as a child row, and the old row will be deleted.

In my example, my parent row will have some general information, and then I'd like to "roll-up" the Director's First and Last name. The screenshot shows my attempt at each method, here's what I'm using in the parent DIR FNAME column:

=INDEX(CHILDREN(ROLE@row), MATCH("Director", CHILDREN(ROLE@row)), 4)

And here's VLOOKUP in the parent DIR LNAME column:

=VLOOKUP("Director", CHILDREN(ROLE@row:LAST@row), 2, false)

I think my greatest issue is using CHILDREN and @row appropriately to define the range. Any help would be greatly appreciated!

Paul Newcome

You cannot pull from a column that is not within the range specified in the INDEX function.

The range you specify in the INDEX function should be the column you want to pull from. The range in the MATCH function is the column you want to match on.

When I posted the formula in my last comment, I didn't look very closely at your ranges other than that they were both single columns. It should look more like this...

=INDEX(CHILDREN(FIRST@row), MATCH("Director", CHILDREN(ROLE@row), 0))

=INDEX(CHILDREN(LAST@row), MATCH("Director", CHILDREN(ROLE@row), 0))

Paul Newcome

The issue with your INDEX/MATCH is that you are specifying to pull from the 4th column in the range referenced by the INDEX function (CHILDREN(ROLE@row)) but you only have 1 column selected. If you are only referencing 1 column for the INDEX function, then you can leave that portion blank. I also suggest specifying to look for an exact match in your MATCH function. It provides more reliable and accurate results.

=INDEX(CHILDREN(ROLE@row), MATCH("Director", CHILDREN(ROLE@row), 0))

The problem with the VLOOKUP is that you are trying to reference multiple cells/columns in the CHILDREN function. Try changing that over to an INDEX/MATCH similar to the above.

weswillis

https://community.smartsheet.com/discussion/comment/255894#Comment_255894

Ok, this actually moves me forward. Now I'm pulling the value of the cell in the column referenced in the index/match ("Director") but if I'm reading your reply correctly, I can't instruct it to pull from a separate column on the "matching" row? When I specify a column (4 in this example, since that's relative to the first sheet column. I've tried 2 since that would be relative to index column) using the solution you provided, it breaks the formula:

=INDEX(CHILDREN(ROLE@row), MATCH("Director", CHILDREN(ROLE@row), 0), 4)

Am I missing something or is it just not possible to pull from another column (in this case grab the first name from the row with the role of director).

Paul Newcome

You cannot pull from a column that is not within the range specified in the INDEX function.

The range you specify in the INDEX function should be the column you want to pull from. The range in the MATCH function is the column you want to match on.

When I posted the formula in my last comment, I didn't look very closely at your ranges other than that they were both single columns. It should look more like this...

=INDEX(CHILDREN(FIRST@row), MATCH("Director", CHILDREN(ROLE@row), 0))

=INDEX(CHILDREN(LAST@row), MATCH("Director", CHILDREN(ROLE@row), 0))