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.
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:Â 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)
- 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
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.
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.
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.
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.
