Nazmul Hossain Shovon
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.
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))
- 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)
- 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)
- 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)
- 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","")
- 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.
- Secondly, select Insert in the VBA window and click on Module.
- 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
- 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.
