VLOOKUP cross-sheet, if the search value's parent = @row

Ingrid O

Hi all, 

I'm trying to populate an entire column on a sheet using both cross-sheet and in-sheet references. I'm trying to return a value according to the following logic:  

Looking for cells cross-sheet that contain "Opening Date" and returning the value from the cell to it's right (also cross-sheet) if that particular instance of "Opening Date" is a child row to the parent row (also cross-sheet) that matches the unit name identified @row in the current sheet. 

=IF({2020 Unit Opening Schedule Range 2} = "Opening Date", VLOOKUP(@row, {2020 Unit Opening Schedule Range 7}, 3),{2020 Unit Opening Schedule Range 8})

Anyone have any insight into how to make this work or what other formulas I should be nesting? 

Thank you! 

L_123

Issue is the children column. I would solve this by using the index(collect( formulas with a helper column. Inside of the helper column I would type

=iferror(parent(),"")

 

https://help.smartsheet.com/function/collect