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 illustration.


Download Practice Workbook

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


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

Let’s get introduced to our dataset first which represents some fruits’ prices.


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.

Steps:

  • Activate Cell C13.
  • Type the following formula in it-
=INDIRECT(ADDRESS(C11,C12))
  • Finally, just hit the Enter button to get the output.

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

⏬ Formula Breakdown:

➥ ADDRESS(C11,C12)
The ADDRESS function will return the default cell reference for row number 8 and column number 2. So it will return as-
“$B$8”

➥ INDIRECT(ADDRESS(C11,C12))
Finally, the INDIRECT function will return the value of that cell according to the cell reference and that is-
“Watch”

Read More: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)


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

To get a value you can use the INDEX function to refer a cell by row and column number.

Steps:

  • Write the following formula in Cell C13
=INDEX(A1:C9,C11,C12)
  • Then to get the result, press the Enter button.

Use INDEX Function to Reference Cell by Row and Column Number


Similar Readings


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 give input the row number and column number as text reference. Let’s see how to do it.

Steps:

  • In Cell C13, type the following formula
=INDIRECT("R" & 8 & "C" & 2,FALSE)
  • Later, hit the Enter button for the result.

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


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 UseReference using VBA and then we’ll apply it to our sheet.

Steps:

  • Right-click on the sheet title.
  • After that, select View Code from the Context menu.

A VBA window will open up. Or you can press Alt+F11 to open the VBA window directly.

Reference Cell by Row and Column Number Applying User Defined Function

  • Now click Insert > Module.

Reference Cell by Row and Column Number Applying User Defined Function

  • At this moment, type the following codes in the module-
Option Explicit
Function UseReference(row As Integer, col As Integer)
    UseReference = ActiveSheet.Cells(row, col)
End Function
  • Then no need to run the codes, just minimize the VBA window and go back to your sheet.

Reference Cell by Row and Column Number Applying User Defined Function

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 B8, type the following formula in Cell C13-
=UseReference(C11,C12)
  • Finally, just press the Enter button to finish.

And have a look, we have got the correct value.

Read More: Excel VBA: Set Range by Row and Column Number (3 Examples)


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.


Similar Readings

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo