(Solved) – How to auto fill a specific complex formula after inserting new row to a defined Excel table

  • by

I’ve been looking for a way to automatically insert a row, where a column contains an Index(Match) formula.

I’ve looked at several tutorials and forum where it says formulas should auto populate because it’s part of the Excel table function, especially if auto-fill feature is enabled. However, my formula is the following, and will not process.

=INDEX(‘Lookup Tables’!$M$20:$M$26,(MATCH(H2,’Lookup Tables’!$N$20:$N$26,0)))

This formula is in Cell G2. Adding info into cells G3, G4 would make the following formula on the column to the right.

=INDEX(‘Lookup Tables’!$M$20:$M$26,(MATCH(H3,’Lookup Tables’!$N$20:$N$26,0))),

=INDEX(‘Lookup Tables’!$M$20:$M$26,(MATCH(H4,’Lookup Tables’!$N$20:$N$26,0)))

Basically, when a new value is entered on H3, H4, H5, etc, the =index(match) formula should automatically populate, but this doesn’t work.

I thought of just adding an =iferror(Formula,” “) but that just expands the table indefinitely and adds too many blank columns (my table has over 20 columns).

Only way I could think was to write a VBA script, but I’m stuck on what to do or where to even start.

Any advice would be appreciated. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *