How to Use Excel INDEX Formulas to Reference Rows Between Tables
Question details
The user wants to link rows from a source table on one worksheet to a representation table on another using reference row numbers, ensuring formulas update dynamically without requiring unique lookup labels.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Referencing data across multiple tables or worksheets dynamically using specific row numbers and column offsets.
- Observed behavior
- Needs a reliable method to pull specific columns based on a reference row number without manually rewriting formulas when new source rows are inserted.
Ensure both your source table and target representation table are clearly structured, and note the specific cell containing your reference row number before building the formulas.
Use INDEX and Named Ranges to Link Rows Dynamically
By combining the INDEX function with named ranges and an absolute reference to a row number, you can reliably pull data between worksheets without needing unique lookup IDs.
While VLOOKUP is useful when you have a unique identifier in the first column, INDEX provides much more flexibility when dealing with structural changes. By defining a named range for your source data, your formulas become easier to read and less prone to errors when columns or rows are inserted.
Using a dedicated reference row cell allows you to easily shift which record the target table is displaying simply by changing one number.
Navigate to your source worksheet (Table 1). Select the entire data area you want to reference. Go to the Formulas tab, select Define Name, and name the range something recognizable, such as Table1Data.
On your target worksheet (Table 2), designate a specific cell to hold the row number you wish to reference from Table 1. For example, enter the desired row number into cell I5.
In the cell where you want to display the data, enter the INDEX formula locking the reference row cell. For example, to pull the 1st column, type =INDEX(Table1Data,$I$5,1). To combine multiple columns into one cell, use the ampersand operator, such as =INDEX(Table1Data,$I$5,15)&" ("&INDEX(Table1Data,$I$5,13)&")".
If you need to drag the formula across multiple columns dynamically, use a header-based column offset. In the first target cell, enter a formula like =INDEX(Table1Data,$I$5,C$7+3) where C$7 contains a relative offset value, and drag the fill handle to the right.

Master Cross-Table Referencing with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like INDEX and VLOOKUP, allowing you to seamlessly link and manage data across multiple worksheets with its powerful and intuitive Name Manager.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your source and target tables.
- 2. Define the Source Range: Highlight your source data, navigate to the Formulas tab, and click Name Manager to create a named range (e.g., Table1Data).
- 3. Apply the INDEX Formula: In your target worksheet, select the destination cell and input your =INDEX() formula referencing the named range and the cell containing your target row number.
- 4. Drag to Fill: Press Enter to execute the formula, then click and drag the fill handle across adjacent cells to instantly populate the rest of the table.

Frequently Asked Questions
Why should I use INDEX instead of VLOOKUP for referencing tables?
INDEX is more versatile than VLOOKUP because it does not require a unique identifier in the first column of your table. Additionally, INDEX formulas are less likely to break when new columns are inserted into your source table.
How do I fix a #REF! error in my INDEX formula?
A #REF! error occurs when the formula refers to a row or column number that is outside the boundaries of your defined array. Check the row and column arguments in your INDEX formula and ensure they do not exceed the actual size of your named range.
Can I combine INDEX with MATCH to find rows automatically?
Yes. While using a static row number works well for direct mapping, combining INDEX with the MATCH function allows you to dynamically look up row numbers based on specific criteria, creating a highly flexible two-way lookup system.




