Use a Cell Value as a Row Number in Excel References
Question details
Retrieve values from multiple columns using a row number stored in another cell.

- 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.
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.
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.
Type the desired worksheet row number into cell B5.
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.
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.

Copy one INDEX formula across a horizontal template
Use column-number labels to retrieve nonadjacent columns with a formula you can copy across.
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. Open the workbook: Open WPS Spreadsheets and select Open to load your workbook.
- 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. Enter INDEX: Select J2 and enter =INDEX($A:$G,$B$5,J$1).
- 4. Fill the template: Drag the fill handle from J2 through M2 to retrieve values from columns A, B, E, and G.

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.




