Function Problems

How to Use Excel INDEX Formulas to Reference Rows Between Tables

Huma Ashraf ChHuma Ashraf Ch Nov 3, 2026 870 views

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.

How to Use Excel INDEX Formulas to Reference Rows Between Tables
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a Named Range for the Source Table

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.

2
Set Up the Reference Row Number

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.

3
Write the INDEX Formula for Specific Columns

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)&")".

4
Create Horizontally Copied Formulas

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.

Use INDEX and Named Ranges to Link Rows Dynamically
Formula Tip: Using absolute references (like $I$5) ensures that the row number reference does not shift when you copy and paste your formulas to other cells.

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. 1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your source and target tables.
  2. 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. 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. 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.
100% compatible with Microsoft Excel formulas and .xlsx file formats.Built-in Name Manager makes defining and updating data ranges effortless.Lightweight, fast execution even when processing large tables and complex cross-sheet references.
microsoft office alternative - wps office

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.