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

Get FREE Advanced Excel Exercises with Solutions!

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 that will be very useful in any Excel-related task.


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

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: Excel VBA to Find Cell Address Based on Value


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


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.


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.


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.


Download Practice Workbook

You can download the practice workbook from here.


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. If you have any queries, please let me know in the comments.


Related Articles


<< Go Back to Excel ADDRESS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo