Function Problems

Reference an Excel Row Using a Number in Another Cell

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

Question details

Use the number stored in G5 as a dynamic row number to return a value from column A.

Reference an Excel Row Using a Number in Another Cell
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants a formula in B4 that looks up a cell in column A without typing its row number directly into the formula.
Observed behavior
When G5 contains 2, the formula should return the value from A2.
Before you start

Enter a positive whole-number row reference in G5 and make sure the corresponding cell in column A contains the value you want to return.

Solution 1Recommended

Use INDEX to Return a Value from the Specified Row

Use INDEX for a nonvolatile formula that is generally more efficient in large workbooks.

1
Enter the formula

Select B4, type =INDEX(A:A,G5), and press Enter.

2
Check the result

Enter 2 in G5 to return the value from A2. Change G5 to another valid row number to return the corresponding value from column A.

Use INDEX to Return a Value from the Specified Row
Use a valid row number: G5 should contain a positive whole number within the worksheet's row limit.
WPS Spreadsheets

Create a Dynamic Row Reference in WPS Spreadsheets

Use the INDEX formula in WPS Spreadsheets to return a value from column A based on the row number in G5.

  1. 1. Open the workbook: Open WPS Spreadsheets and select the workbook containing your data.
  2. 2. Enter a row number: Select G5 and enter a positive whole number, such as 2.
  3. 3. Add the formula: Select B4, enter =INDEX(A:A,G5), and press Enter.
  4. 4. Verify and save: Confirm that B4 shows the value from the specified row in column A, then choose File > Save.
Update the referenced row by changing G5Use the nonvolatile INDEX function for dynamic referencesOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
QA img-9

Frequently Asked Questions

What formula returns the value from column A using the row number in G5?

Enter =INDEX(A:A,G5) in the result cell. If G5 is 2, the formula returns the value from A2.

How can I build the cell reference as text?

Enter =INDIRECT("A"&G5) in the result cell. The formula combines A with the row number in G5 to form a reference.

Why might INDEX be preferable to INDIRECT?

INDEX is nonvolatile, while INDIRECT is volatile and recalculates more often. INDEX is generally more efficient when a large workbook contains many formulas.

How do I reference a different column?

Replace A:A in the INDEX formula with the desired column. For example, =INDEX(C:C,G5) returns the value from column C at the row specified in G5.