This post will guide you how to retrieve values using references based on Row and Column numbers in excel. How to get the cell value based on row and column numbers with an excel formula or user defined function in excel.
Get Cell Value Based on Row and Column Numbers
Assuming that there is a row number in cell D1 and there is a column number in cell D2, if you want to retrieve the cell value based on the specified row number and column number, just type the following formula in cell D3:
You can also write a User Defined Function to get content of a cell given the row and column numbers. Just do the following steps:
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module
#4 paste the below VBA code into the code window. Then clicking “Save” button.
Function GetCellValue(row As Integer, col As Integer) GetCellValue = ActiveSheet.Cells(row, col) End Function
#5 back to the current worksheet, try to enter the below formula in Cell D4.
- Excel ADDRESS function
The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
- Excel INDIRECT function
The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….