Index Match formula populating when form added, without cross sheet being populated

Stephanie Allison

I have a sheet for division entry. When it is approved by the director a workflow copies the entire row to a master sheet, where the commissioner approves. All approvals are a drop down symbol.

When the commissioner approves, there is an index/match formula on the division sheet, indicating if commissioner approves or rejects.

This works just fine. But when a new row is added, either with a form or directly on the sheet, the new entry populates the commissioner approval the same as the prior row. Effectively populating prior to the commissioner approval or rejection being applied on the master sheet.

The formula being used is

=INDEX({FY24 Travel Plan - Master Commissioner Approval}, MATCH([ID Number (Automatic)]@row, {FY24 Travel Plan - Master ID Number}))

I've tried having the formula as a cell formula and a column formula.

EDITED TO ADD: I've also added ,"0") at the end as well, resulting in an #invalid Data message.

Any suggestions?

Stephanie Allison

I might have solved this by using an index collect formula and including an iferror statement.

New formula is:

=IFERROR(INDEX(COLLECT({FY24 Travel Plan - Master Commissioner Approval}, {FY24 Travel Plan - Master ID Number}, [ID Number (Automatic)]@row), 1), "No Match Found")

When adding rows to the division sheet, the commissioner approval column is no longer pre-populated. New rows now indicate "No Match Found".

It could be that an iferror would have resolved, without using index collect. I don't really have time to test, need to move forward!

Genevieve P.

Hey @Stephanie Allison

Thanks for sharing your solution! 🙂