Function Problems

Use a Cell Value as a Row Number in Excel References

Maira MehtabMaira Mehtab Nov 18, 2026 868 views

Question details

Retrieve values from multiple columns using a row number stored in another cell.

Use a Cell Value as a Row Number in Excel References
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to populate a template from different columns without manually changing the row number in every formula.
Observed behavior
The desired formulas return values from the row specified in a separate cell and update when that row number changes.
Before you start

Identify the cell that will hold the row number and the columns you want to retrieve. In the examples below, B5 holds a positive row number, and the source data is in columns A through G.

Solution 1Recommended

Use INDEX to retrieve values from specific columns

Use separate INDEX formulas when you need values from a few known columns in the same row.

1
Enter the row number

Type the desired worksheet row number into cell B5.

2
Enter the formulas

In output cells outside columns A through G, enter =INDEX(A:A,$B$5), =INDEX(B:B,$B$5), =INDEX(E:E,$B$5), and =INDEX(G:G,$B$5) to retrieve values from those columns.

3
Change the selected row

Enter a different row number in B5 to update all four results. The dollar signs keep the B5 reference fixed if you copy a formula.

Use INDEX to retrieve values from specific columns
Use a valid row number: B5 must contain a positive row number within the worksheet. When INDEX uses an entire column, the row number corresponds to the worksheet row.
WPS Spreadsheets

Use a Cell as a Row Reference in WPS Spreadsheets

In WPS Spreadsheets, use INDEX with a row number stored in a cell to retrieve values from multiple columns and fill a horizontal template.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to load your workbook.
  2. 2. Set the row and columns: Enter the desired row number in B5, then enter 1, 2, 5, and 7 in J1 through M1.
  3. 3. Enter INDEX: Select J2 and enter =INDEX($A:$G,$B$5,J$1).
  4. 4. Fill the template: Drag the fill handle from J2 through M2 to retrieve values from columns A, B, E, and G.
Retrieve values from nonadjacent columns using one row-number cell.Copy an INDEX formula across a template using column-number labels.Open and edit Microsoft Excel XLS and XLSX files.Use a familiar spreadsheet interface for formulas and worksheets.
microsoft office alternative - wps office

Frequently Asked Questions

How do I keep the row-number cell fixed when copying a formula?

Use an absolute reference such as $B$5 in the formula. For example, =INDEX(A:A,$B$5) continues to refer to B5 when copied.

Can I retrieve values from nonadjacent columns with one formula?

Yes. Enter the column positions, such as 1, 2, 5, and 7, across J1:M1. Enter =INDEX($A:$G,$B$5,J$1) in J2 and copy it across.

Why does INDEX return a reference error?

Check that B5 contains a positive row number within the source range and that the column number is within the selected range. For A:G, valid column positions are 1 through 7.

Does the row number always match the worksheet row?

It does when the INDEX range starts at row 1, as in A:A or A:G. If the range starts at a later row, INDEX counts from the first row of that range instead.