How to Reference Cell by Row and Column Number in Excel (4 Methods)

It’s quite common to use a cell reference in Excel. But except the traditional way, there are many ways by which you can reference cells by row and column number in Excel. This article will show you those 4 effective ways to reference cell by row and column number in Excel with sharp steps and vivid illustrations. In the following overview image, you’ll see different methods to reference cell by row and column number in Excel.

How to Reference Cell by Row and Column Number in Excel: 4 Easy Ways

Let’s get introduced to our dataset first which represents some employees’ information about an organization. We’ll see 4 suitable ways to reference cell by row and column number in Excel below using this dataset.

1. Use INDIRECT and ADDRESS Functions to Reference Cell by Row and Column Number

While using the ADDRESS function within the INDIRECT function, we can reference a cell by row and column number to get a value. We’ll get the value with respect to the Excel sheet’s original row and column number if we use these 2 functions together. This is the advantage of this method. Here, we want to get the value that lies in row number 8 and column number 3 of the current sheet which is David.

Steps:

• Activate Cell D17 and type the following formula in it-

• Finally, just hit the Enter button to get the output.

⏬ Formula Breakdown:

➥ ADDRESS(B17,C17))

The ADDRESS function will return the default cell reference for row number 8 and column number 3. So it will return as-

“$C$8”

➥ INDIRECT(ADDRESS(B17,C17))

Finally, the INDIRECT function will return the value of that cell according to the cell reference and that is-

“David”

Read More: How to Display Text from Another Cell in Excel

2. Use INDEX Function to Reference Cell by Row and Column Number

When you want to refer to a cell by row and column number with respect to your given range, not the actual row and column number of your Excel sheet, you can use the INDEX function. Suppose, we have the previous dataset range B4:E14 but we named the range Employee_Info by using the Name Box feature. So, when we put the original row and column numbers into the INDEX function, it’ll give us the wrong result. But putting the row and column numbers with respect to our given range will give us the correct result.

Steps:

• We put the following formula in Cell D17 and it is giving us the wrong result because we put the actual row and column number of our Excel sheet into this formula-

• Here, the Employee_Info part is the range of our dataset, from where the INDEX function will search for the value. And B17 and C17 is denoting the actual row number 8 and column number 3 of your Excel sheet.

• So, put the correct formula in cell D21 to get the correct result with the row and column number with respect to the range Employee_Info–

• Here, B21 and C21 is denoting the row number 5 and column number 2 based on the given range Employee_Info.

Read More: How to Reference Text in Another Cell in Excel

3. Use Text Reference Within the INDIRECT Function to Reference Cell by Row and Column Number

Again we’ll use the INDIRECT function here. But here we’ll input the row number and column number as text reference. This method is similar to our first method. But the difference is that here we are putting the cell reference manually into the INDIRECT function, as we are using this function alone. You can use this method when you have a small dataset and want to use only one function. But if you have a large dataset, then I’ll recommend the first method.

Steps:

• In Cell D17, type the following formula and hit the Enter button for the result–

• This function will search for the value from row number 7 and column number 3 which is Johnson and brings it here. We put FALSE because we are using the R1C1 style here.

Read More: How to Use Variable Row Number as Cell Reference in Excel

4. Reference Cell by Row and Column Number Applying User Defined Function

In this method, we’ll do the task in a bit different way. First, we’ll make a User Defined Function named CellReference using VBA and then we’ll apply it to our sheet. This method is useful when you are working with Excel versions older than Excel 2007. The INDIRECT, ADDRESS and INDEX functions are not available in Excel versions before Excel 2007. So, in those versions, you can reference cell by row and column number by using this method.

Steps:

• Go to the Developer tab and click on the Visual Basic menu to open the Visual Basic Editor.

• Select Module under the Insert tab for inserting the VBA code and put the following code there-

Option Explicit
Function CellReference(row As Integer, col As Integer)
    CellReference = ActiveSheet.Cells(row, col)
End Function

• Then, just save the code by pressing Ctrl + S together, no need to run Just minimize the VBA window and go back to your sheet.

• Now have a look that our function is ready to use. We’ll have to give just the row number and column number and it will return the value according to that reference.
• To get the value from Cell C7, type the following formula in Cell D17–

• Finally, just press the Enter button to finish.
• And have a look, we have got the correct value, Johnson.

Read More: How to Find and Replace Cell Reference in Excel Formula

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Conclusion

I hope the procedures described above will be good enough to reference cell by row and column number in Excel. Feel free to ask any question in the comment section and please give me feedback.

Related Articles

• How to Use Cell Value as Worksheet Name in Formula Reference in Excel
• How to Use OFFSET for Cell Reference in Excel
• How to Reference a Cell from a Different Worksheet in Excel
• How to Reference Cell in Another Sheet Dynamically in Excel
• Excel VBA Examples with Cell Reference by Row and Column Number
• Excel VBA: Cell Reference in Another Sheet 

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel