# Retrieving Ancestor (date) value from multi-level parent-child rows

Hi All,

I used the following formula to retrieve ancestor (first parent) date value of the multi-level parent-child rows for each row.

=LEFT(INDEX(JOIN(ANCESTORS(Date@row), ","), 1), 8)

The issue that I'm facing is the return value being "TEXT" despite having "DATE" column type. Since I have to use this return value in comparing other dates, suggest any other alternative to retrieve the value in DATE format

## Best Answer

• ✭✭✭✭✭✭
Answer ✓

Hi @Suriya

You can use the Header value as the search value of the MATCH function.

Find the relative position of the first ANCESTORs value of the Header in the Header:Header range gotten by INDEX(,1).

In English, the formula means to find the top ANCESTORS of a given header and use the relative position to get the corresponding date value.😀

=IF(COUNT(ANCESTORS(Header@row)) = 0, Date@row, INDEX(Date:Date, MATCH(INDEX(ANCESTORS(Header@row), 1), Header:Header, 0)))

## Answers

• ✭✭✭✭✭✭
Answer ✓

Hi @Suriya

You can use the Header value as the search value of the MATCH function.

Find the relative position of the first ANCESTORs value of the Header in the Header:Header range gotten by INDEX(,1).

In English, the formula means to find the top ANCESTORS of a given header and use the relative position to get the corresponding date value.😀

=IF(COUNT(ANCESTORS(Header@row)) = 0, Date@row, INDEX(Date:Date, MATCH(INDEX(ANCESTORS(Header@row), 1), Header:Header, 0)))

• It works! Thank you @jmyzk_cloudsmart_jp

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!