Using CHILDREN() to Define Range in VLOOKUP()

jjShnabadu

I have a parent row on Row 1 with three indented child rows. I want to populate a cell in the parent row with the first name from a child row that matches a value in one of the columns.

There will be other parent rows with their own children, and the number of children may vary. Each parent row represents a "family", and each child row represents a "student."

I'd like to use a VLOOKUP() formula with a dynamic range from the child rows.

Example: Populate [Current Grade: 7]1 with the first name of a student with the value "2019-2020" in the column [Enrolled Grade: 7].

Screenshot: VLOOKUP_ERROR_04.png

I'm attempting to do this with a VLOOKUP() formula in [Current Grade: 7]1.

If I explicitly define the range in VLOOKUP(), it works:

=VLOOKUP("2019-2020",[Enrolled Grade: 7]2:[First Name]4,4,false)

However, the number of child rows for any given parent will vary, and I don't want to have to explicitly define the range for the formula in each parent row. Instead, I'd like to use the CHILDREN() function to define the range, but I get an error with:

=VLOOKUP("2019-2020",CHILDREN([Enrolled Grade: 7]1:[First Name]1),4,false)

The only difference is that I'm using CHILDREN() to define the range. That works just fine with the COUNT() and SUM() functions, but it doesn't seem to work with VLOOKUP(). I'm not sure why, since in both cases, it's defining a range.

For example, this works fine:

=COUNTIF(CHILDREN([Enrolled Grade: 7]1:[First Name]1), "2019-2020")

In the above, the range is defined as: CHILDREN([Enrolled Grade: 7]1:[First Name]1)

If I use that same range definition in VLOOKUP(), I get an error for incorrect argument set.

What am I doing wrong?

If it's a syntax error, please help me get it right.

If it's a limitation of the VLOOKUP() function, what other options do I have to dynamically define a range (from child rows) that will work in the VLOOKUP() function?

Maybe I can construct the range by getting the starting *child* cell of the first range column and the last *child* cell of the last range column. If that would work, how do I do that?

Other options?

Thanks!

Richard

 

jjShnabadu

I might have found a solution using INDEX() and MATCH():

=INDEX(CHILDREN([First Name]1), MATCH("2019-2020", CHILDREN([Enrolled Grade: 7]1), 0))

I would still like to know if there's a way to do it with VLOOKUP() or if using CHILDREN() to define a range in VLOOKUP() is not possible...

Any guidance on this is much appreciated. Thanks!