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

Ingrid O
Ingrid O
edited 12/09/19 in Formulas and Functions

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! 


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!