INDEX / MATCH "No Match" Error

Bridget Jones

Hello all,

I'm attempting to create a status log, where each week individuals enter their updates in the row with that week's date. I want the parent row to then return a) the current week's date in the "date" column and b) the latest update in the "key accomplishments" and "key upcoming items" columns.

I've created a helper column "this week" and set the formula to return a "1" if the "Date" column is within the next 7 days.

Unfortunately, my formula for the index / match in the parent row of the "key accomplishments" and "key upcoming items" columns isn't working - I get a "no match" error.

Here's my formula:

=INDEX([Key Accomplishments]2:[Key Accomplishments]18, MATCH("1", [This Week]2:[This Week]18), "0")

Thank you for any help!

Screen Shot 2021-01-27 at 6.45.32 AM.png

Paul Newcome

Try this...

=INDEX(CHILDREN(), MATCH(1, CHILDREN([This Week]@row), 0))

Bridget Jones

Incredible, thank you so much! I didn't think to do "children" :) Have a great day, Paul! I appreciate the quick reply!

Paul Newcome

Happy to help. 👍️

Using the CHILDREN function also has the added benefit of not needing to adjust the ranges in your formulas when child rows are added or deleted.