Function Problems

Excel INDEX Formula to Return a Row by Row Number

Tauseeq MagsiTauseeq Magsi Nov 13, 2026 869 views

Question details

Return values from a specified row in another workbook and copy the formula across to retrieve its columns.

How to Return a Row by Row Number with Excel INDEX
Product
Microsoft Excel
Device & OS
not provided
Scenario
A cell contains the source row number, and the source data runs from column A through column Z.
Observed behavior
The desired result is a row of values corresponding to the row number in the input cell.
Before you start

Open both workbooks and confirm the source file name, worksheet name, and columns. Enter the source worksheet row number in T2; for example, enter 2 to retrieve row 2.

Solution 1Recommended

Use INDEX and copy the formula across

Use this method when T2 contains the source row number and the source data starts in column A.

The reference $T2 keeps column T fixed as you copy the formula across, while A:A changes to B:B, C:C, and so on.

1
Enter the row number

In the destination workbook, select T2 and enter the worksheet row number you want to retrieve.

2
Enter the INDEX formula

Select U2 and enter =INDEX('[CT as of 1 Apr.xlsx]Sheet1'!A:A,$T2). Replace the example file name and Sheet1 with the actual source workbook and worksheet names.

3
Copy across

Drag the fill handle from U2 across to AT2 to retrieve source columns A through Z. Check that the formulas change from A:A to B:B and subsequent columns.

Use INDEX and copy the formula across
External workbook reference: Keep the source workbook open while setting up the formula. If Excel cannot find it later, update the external link or reopen the source workbook.
Work with spreadsheet formulas in WPS Office

Retrieve a Workbook Row with INDEX in WPS Spreadsheets

Open the source and destination files in WPS Spreadsheets, use INDEX with the row number in T2, and copy the formula across to retrieve each source column.

  1. 1. Open both workbooks: In WPS Spreadsheets, open the source workbook and the destination workbook using File > Open.
  2. 2. Enter the row number: In the destination sheet, select T2 and enter the row number to retrieve.
  3. 3. Enter the formula: In U2, enter =INDEX('[CT as of 1 Apr.xlsx]Sheet1'!A:A,$T2), replacing the example workbook and sheet names with your own.
  4. 4. Fill across: Drag the fill handle from U2 to AT2 to retrieve values from source columns A through Z, then check the results.
Use INDEX to retrieve values by row number.Open and edit Microsoft Excel XLSX workbooks.Copy formulas across columns with the fill handle.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use $T2 instead of T2?

The dollar sign locks column T when you copy the formula horizontally. The row remains relative, so copying the formula down changes T2 to T3.

How do I retrieve a different source row?

Change the number in T2. For example, enter 5 to return values from row 5 of the source worksheet.

What if the source data starts in column B?

Change A:A to B:B in the formula in U2, then copy it across. Each subsequent formula will refer to the next source column.

What should I check if the formula returns an error?

Check that T2 contains a valid worksheet row number and that the workbook and sheet names in the formula are correct. Open the source workbook and check the external link if Excel cannot find the file.