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:


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!

Best Answer


  • Paul Newcome
    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
    weswillis ✭✭
    edited 07/08/20

    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).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!