Function Problems

How to Update INDEX and MATCH Ranges Automatically in Excel

Algirdas Jasaitis Oct 1, 2026 869 views

Question details

The user needs to configure INDEX and MATCH formulas so their data ranges expand and update automatically when new rows of data are added.

How to Update INDEX and MATCH Ranges Automatically in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Adding new data to a source sheet and needing the lookup formulas to instantly recognize the new rows without requiring manual edits.
Observed behavior: By default, fixed ranges in formulas do not include newly added rows, forcing the user to manually adjust the range references each time data updates.

Before you start

Ensure your source data does not contain completely blank rows or merged cells, as these can disrupt structured table references and entire-column lookups.

Solution 1Recommended

Convert Data to an Excel Table (Structured References)

Using Excel Tables is the most reliable and efficient way to make formula ranges expand dynamically when new data is added.

Excel Tables automatically expand to include new rows and columns. When you use structured references (the table's name and column headers) in your formula, they dynamically update without slowing down your workbook.

Format as Table

Select your source data range and press Ctrl + T (or go to Insert > Table) to convert it into an Excel Table.

Name the Table

Go to the Table Design tab on the ribbon and give your table a descriptive name, such as 'SalesData', in the Table Name box.

Write the Formula

Rewrite your INDEX and MATCH formula using structured references instead of cell ranges. For example: =INDEX(SalesData[Price], MATCH(A2, SalesData[ID], 0)).

Add New Data

Add new rows to the bottom of your table. The formula will automatically include the new data in its search range without any manual edits.

Convert Data to an Excel Table (Structured References)

Best Practice: Using Tables prevents the performance lag associated with referencing entire columns, making it ideal for larger datasets.

Solution 2

Use Entire-Column References

A quick alternative that references the whole column, evaluating any data placed in that column regardless of the row.

Solution 3

Leverage Dynamic Arrays (Excel 2021 and Newer)

For newer versions of Excel, dynamic arrays automatically spill results based on dynamic criteria.

Advanced Spreadsheet Software

Automate Your Lookup Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formulas like INDEX and MATCH, as well as Excel Tables and structured references. You can easily manage expanding datasets without modifying your formulas manually.

1. Open Your Data: Launch WPS Spreadsheet and open your existing workbook.
2. Create a Table: Select your source data range and go to Insert > Table to convert it into a dynamic table.
3. Apply the Formula: Enter your =INDEX() and MATCH() formula using the newly created table column names (e.g., Table1[Column A]).
4. Add Records: Add new records to the bottom of the table and watch your lookup results update automatically.

Fully compatible with Microsoft Excel formulas (.xlsx), including INDEX, MATCH, and XLOOKUP.Built-in support for Tables and structured references for dynamic, expanding data ranges.Free, lightweight, and fast performance even when working with entire-column lookups on large files.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX and MATCH formula return #N/A when I add new data?

This happens if your formula uses absolute or fixed ranges (like $A$2:$A$50). When data is added to row 51, the formula doesn't see it. Converting the range to an Excel Table or using whole-column references resolves this issue.

Does using entire-column references slow down my spreadsheet?

Yes, referencing entire columns (like A:A) forces the spreadsheet to evaluate over a million rows. While fine for smaller files, it can cause calculation lag in heavy workbooks. Using Excel Tables is a much more efficient alternative.

Can I use XLOOKUP instead of INDEX and MATCH for dynamic ranges?

Absolutely. XLOOKUP is a modern alternative that is simpler to write. Like INDEX and MATCH, XLOOKUP seamlessly supports structured table references and entire-column arrays, updating dynamically when new data is added.

Excel & Data Problems