How to Get Cell Value by Address in Excel (6 Simple Methods)

In this tutorial, I am going to show you 6 simple methods of how to get cell value by address in excel. You can quickly use these methods even in large datasets to get the value of any specific criteria-based cells. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


6 Simple Methods to Get Cell Value by Address in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we inserted and formatted our dataset to make it visually more clear. For all the datasets, we have the 3 columns as Items, Find 1, and the last one for the formula result.

excel get cell value by address


1. Get Cell Value Using ADDRESS Function

The ADDRESS function in excel can return the cell reference number if we provide the specific row and column numbers. Let us see how we can use this function to get a specific cell value.

Steps:

  • First, go to cell D4 and insert the following formula:
=INDIRECT(ADDRESS(10,2))

address function to get cell value in excel

  • Next, press Enter and this will find the item that you are looking for.
  • Similarly, you can copy this formula to other cells and modify the criteria to find other items.

Read More: How to Return Cell Address Instead of Value in Excel (5 Ways)


2. Utilizing INDEX Function

The INDEX function in excel can get a certain cell value from a pre-specified range of data. In this method, we will see how to apply this function.

Steps:

  • To begin with, double-click on cell D6 and enter the below formula:
=INDEX(B4:D10,4,1)

index function to get cell value by address in excel

  • Now, press the Enter key, which should retrieve the cell value according to the set criteria.


3. Applying INDIRECT Function

The INDIRECT function in excel locks a specific cell reference that we insert as input. We can use this function to get a cell value by providing the address as its input.

Steps:

  • To begin this method, double-click on cell D6 and insert the formula below:
=INDIRECT("R" & D4 & "C" & D5,FALSE)

indirect function to get cell value by address in excel

  • Finally, after pressing Enter, you should get the desired item in cell D6.

Read More: Example of Cell Address in Excel (5 Ideal Cases)


4. Combining MAX and MIN Functions

The MAX function and the MIN function in excel take a range of numeric data cell addresses as input and get the maximum and minimum cell values. Follow the steps below to use these functions.

Steps:

  • To start this method, navigate to cell E4 and type in the following formula:
=MAX(C5:C10)

max and min functions to get cell value by address in excel

  • Next, press the Enter key and insert the following formula in the cell E5:
=MIN(C5:C10)
  • As a result, this should give the maximum and minimum values of the sales units.

Read More: How to Use Cell Address in Excel Formula (4 Useful Methods)


5. Finding Cell Column Letter

We can use the ADDRESS function with the SUBSTITUTE function in excel to get a column letter value by providing its column number. For this, we have to input the cell address inside the formula as we shall see below.

Steps:

  • First, go to cell C5 and insert the following formula:
=SUBSTITUTE(ADDRESS(5,B5,4),"5","")

column letter to get cell value by address in excel

  • Then click on any empty cell and copy this formula to the below.
  • Immediately, you should see the column letter of the respective input column numbers.

Read More: How to Copy Cell Address in Excel (5 Simple Ways)


6. Using VBA Code

One of the effective ways to get a cell value by providing its address is to use excel VBA code. Also, this code is reusable so we can use them in many instances by doing little modifications.

Steps:

  • Firstly, go to the Developer tab and select Visual Basic.

opening VBA to get cell value by address in excel

  • Secondly, select Insert in the VBA window and click on Module.

inserting module to get cell value by address in excel

  • Next, type in the formula below in the new window:
Function CellValue(rowNum As Integer, colNum As Integer)
CellValue = ActiveSheet.Cells(rowNum, colNum)
End Function

VBA code to get cell value by address in excel

  • After that, navigate to cell D6 and type in the below formula:
=CellValue(D4,D5)

  • Finally, press the Enter key and the VBA function will find the cell value accordingly.

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


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to get a cell value by address in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo