ExcelInsider Team
When we work with large datasets in Excel, we need to directly access a cell based on its row and column number and sometimes even modify it. If you are building dynamic dashboards, automating calculations, or writing VBA scripts, referencing cells programmatically simplifies your workflow and saves a lot of time.
To reference cell by row and column number in excel, follow these steps:
➤ Decide the row and column number you want to refer to.
➤ Click an empty cell (e.g., E4) and enter: =ADDRESS(5, 3)
➤ Use the returned value for further calculations or logic.
In this article, we will explore different functions and formulas like INDEX, INDIRECT, ADDRESS, ROW & COLUMN etc to reference Excel cells by row and column numbers.
Extract Row and Column Numbers Based on a Cell Value
Implementing ROW/COLUMN Function
The ROW and COLUMN functions in Excel are used to identify the row number and column number of a specific cell. These are helpful in dynamic formulas, conditional formatting, or when building automated reports. We use them more often when we need to refer to positions rather than specific values.
Steps:
➤ Open your Excel worksheet with a dataset. You can use any table. For this, we have a student grade table in range A2:C11. We want to get the cell reference of “Math” and also want to retrieve the cell value.
➤ To get the row number of “Math” Click on cell F2 and type:
=ROW(B5)
This will return the row number of cell B5, which is 5.
➤ To get the Column number of “Math” Click on cell F3 and type:
=COLUMN(B5)
This will return the row number of cell B5, which is 2.
➤ To get the cell reference of “Math” , on cell F4 type the formula.
=ADDRESS(F2, F3)
This also dynamically adjusts the result if F2 or F3 changes.
Note:
➥ ROW() with no argument returns the row of the cell the formula is in.
➥ ROW(reference) returns the row number of the referenced cell.
➥ Same applies to COLUMN().
Applying ADDRESS Function
The ADDRESS function in Excel allows us to generate a text-based cell reference from a given numeric row and column values. Use this method when you know the coordinates of a cell but want its address returned in A1 style format, for use in formulas, lookup automation, or dynamic referencing.
We have a dataset where we want to get the cell address where the quantity of “Marker” is stored. “Marker” is at row 5 and column 3 (Quantity column). We will use the ADDRESS(row, col) function to do that.
Steps:
➤ Open your Excel workbook.
➤ Identify the target data and its position. Let’s say you want to reference the cell where the quantity of “Marker” is stored. You find it’s at row 4 and column 3.
➤ In an empty area , cell F2, type the row number 5 and in F3, the column number 3. These values refer to the cell in Row 4, Column 3.
➤ Use the ADDRESS function to return a cell reference. In F4, type the formula:
=ADDRESS(F2, F3)
This returns the reference “$C$5”, based on the row and column you input.
Get a Value Based on Row and Column Numbers in Excel
Combining INDIRECT & ADDRESS Functions
The formula consisting of INDIRECT and ADDRESS functions is one of the most dynamic ways to reference a cell in Excel using just its row and column number. The ADDRESS function returns the cell reference as text (e.g., “B2“), and INDIRECT converts that text into a real, usable reference. This method is used when we want to fetch data based on numeric inputs (like row and column numbers stored elsewhere).
Steps:
➤ Open your dataset. We are using a dataset of a product inventory with columns: Product Name, Category, and Stock Level (placed in columns A, B, and C, respectively from Row 2 onward).
➤ In an empty area, say cell F2, type the row number 4 and in F3, the column number 3. These values refer to the cell in Row 4, Column 3 (in this case, the stock level of the 3rd product.)
➤ Use ADDRESS function to return cell reference. In G2, type the formula:
=ADDRESS(F2, F3)
This returns the reference “$C$4”, based on the row and column you input.
➤ Use INDIRECT to retrieve the actual cell value. To do that, in H2, use:
=INDIRECT(ADDRESS(F2, F3))
This will return the actual value stored in cell C4 (e.g., 45 units).
Use INDEX Function to Retrieve Cell Value
The INDEX function references a specific location in a table or range using row and column numbers. Use it when working with structured data where position-based lookup is required, such as HR records, sales lists, or product inventories etc.
Steps:
➤ Create a structured dataset. For this example, let’s use an Employee Attendance sheet with columns: Employee ID, Name, and Days Present.
➤ Suppose you want to fetch the Days Present for the 4th employee in the list. The data range is A2:C11, the row number is 5, and the column number is 3.
➤ Click any blank cell (e.g., F2) and enter:
=INDEX(A1:C11, 5, 3)
This returns 23, which is the attendance of Diana.
Note:
The array must be defined properly (e.g., A1:C11); always count rows/columns from the start of the array, not from the worksheet.
R1C1 Style for Row and Column Numbers to Get Cell Value
The R1C1 referencing style in Excel allows you to refer to cells directly by their row and column numbers. Instead of using the standard A1 style (like B2), R1C1 lets you reference the same cell as R2C2 (Row 2, Column 2). This method is useful in complex formulas, VBA scripts, or when we are referencing cells dynamically based on numerical indexes.
Steps:
➤ To Enable R1C1 Style in Excel Options: Go to File → Options → Formulas.
➤ Then check the box for R1C1 reference style under the “Working with formulas” section.
➤ Click OK. Now your columns will appear as numbers instead of letters.
➤ We have a dataset of employees with columns: Name (R1C1), Department (R1C2), and Salary (R1C3).
➤ In any empty cell (e.g., R2C6), type
=R10C3.
This refers to Row 10, Column 3, which is the Salary of the employee Ian.
Note:
To go back to regular A1 style, return to File → Options → Formulas, and uncheck the R1C1 box.
Use of Cell Reference In Excel Formula
The use of cell references in formulas is a basic and daily needed thing in Excel. It is needed to refer to another cell’s value using its row and column coordinates. This is useful when we are doing calculations across rows in structured datasets like reports, budgets, and invoices.
Steps:
➤ Open your dataset or excel table. We have a dataset with three columns: Region (A), Sales Rep (B), and Monthly Sales (C). In column D, we will calculate a 10% bonus based on the Monthly Sales in column C. Add a header in D1 as “Bonus (10%)”.
➤ In cell D2, type the formula:
=C2*10%
This formula references the value in cell C2 (Monthly Sales for Alice Johnson) and multiplies it by 10%.
➤ Drag the fill handle from the bottom right corner of D2 down to D11. Excel will automatically adjust the references to match the row (e.g., =C3*10%, =C4*10%, etc.).
Note:
➥ This method of cell reference is known as A1 style.
➥ This method uses relative referencing (e.g., C2) which adjusts as you drag the formula.
Frequently Asked Questions (FAQs)
How to get the cell value based on row and column numbers in Excel?
Use the INDEX function: =INDEX(A1:C10, 3, 2). It can return the value from row 3, column 2 of the range A1 to C10.
How do you reference a cell with row and column number in Excel?
You can use INDIRECT(ADDRESS(row, column)) to dynamically reference a cell.
How to use row and column numbers in Excel?
Use formulas like ROW() and COLUMN() to retrieve the current cell’s row or column, or pass them into INDEX, ADDRESS, or Cells() in VBA.
How to fetch the data from a particular row and column of Excel?
Use INDEX(array, row_num, col_num) to fetch data from a specific location.
What is the difference between MATCH and INDEX?
INDEX returns the value of a cell, while MATCH returns the position of a value in a range.
Concluding Words
We have described some formulas and functions like INDEX(), INDIRECT(ADDRESS()), ROW() & COLUMN() etc. You can pull up cell references using ROW, COLUMN and Address functions. Also you can pull up cell value. There you can use the INDIRECT function. Choose the method that best suits your needs. And let us know if you have any queries.
