Another question about using ancestors

megmeyer

I have a fairly large smartsheet, and I'm trying to use a specific ancestor's value in a Vlookup. It was working before, but now it seems to have stopped, and I don't really know why.

I have the following (times about 100 right now, possibly growing to 200 this year)

The cell with the safe fund and acct # needs to lookup the Store # (01442) in another smartsheet to get the values to put with Safe Fund and Acct#.

It seems to work if I directly reference the cell (=VLOOKUP([Store #]44, {New Stores Parameters Range 1}, 2, false) comes up with 10000, which is correct in this instance). But I would like to be able to copy and paste this formula into each store's cell for the acct# and safe fund.

I currently have

="Safe Fund: " + IFERROR(VLOOKUP([Store #]@row, {New Stores Parameters Range 1}, 2, false), "") + CHAR(10) + "Acct#: " + IFERROR(VLOOKUP([Store Number]@row, {New Store Parameters Range 1}, 3, false), ""),

But it ends up at the error part (with no values).

If I just use =VLOOKUP([Store #]@row, {New Stores Parameters Range 1}, 2, false), it says #NO MATCH.

I need to figure out how to reference the level 0 value of Store #.

Is there a way to do that?

Thanks in advance.

Meg

Paul Newcome

This should do it for you...

INDEX(ANCESTORS([Store #]@row), 1)

Paul Newcome

This should do it for you...

INDEX(ANCESTORS([Store #]@row), 1)

megmeyer

That worked!!! Thank you so much!