What is a cell address in Excel

A cell reference, or cell address, is an alphanumeric value used to identify a specific cell in a spreadsheet. Each cell address contains ‘one or more letters’ followed by a number. The letter or letters identify the column and the number represents the row.

Inhaltsverzeichnis Show

Lookup And Return Cell Address Using the ADDRESS Function
Lookup And Return Cell Address Using the CELL Function
What Do You Mean By Cell Reference in Excel?
Types of Cell Reference in Excel
#1 How to Use Relative Cell Reference?
#2 How to Use Absolute Cell Reference?
#3 How to Use Mixed Cell Reference?
Things to Remember
Recommended Articles

Was this answer helpful?

4 (27)

Thank you. Your Feedback will Help us Serve you better.

When using lookup formulas in Excel (such as VLOOKUP, XLOOKUP, or INDEX/MATCH), the intent is to find the matching value and get that value (or a corresponding value in the same row/column) as the result.

But in some cases, instead of getting the value, you may want the formula to return the cell address of the value.

This could be especially useful if you have a large data set and you want to find out the exact position of the lookup formula result.

There are some functions in Excel that designed to do exactly this.

In this tutorial, I will show you how you can find and return the cell address instead of the value in Excel using simple formulas.

Lookup And Return Cell Address Using the ADDRESS Function

The ADDRESS function in Excel is meant to exactly this.

It takes the row and the column number and gives you the cell address of that specific cell.

Below is the syntax of the ADDRESS function:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

where:

row_num: Row number of the cell for which you want the cell address
column_num: Column number of the cell for which you want the address
[abs_num]: Optional argument where you can specify whether want the cell reference to be absolute, relative, or mixed.
[a1]: Optional argument where you can specify whether you want the reference in the R1C1 style or A1 style
[sheet_text]: Optional argument where you can specify whether you want to add the sheet name along with the cell address or not

Now, let’s take an example and see how this works.

Suppose there is a dataset as shown below, where I have the Employee id, their name, and their department, and I want to quickly know the cell address that contains the department for employee id KR256.

Below is the formula that will do this:

=ADDRESS(MATCH("KR256",A1:A20,0),3)

In the above formula, I have used the MATCH function to find out the row number that contains the given employee id.

And since the department is in column C, I have used 3 as the second argument.

This formula works great, but it has one drawback – it won’t work if you add the row above the dataset or a column to the left of the dataset.

This is because when I specify the second argument (the column number) as 3, it’s hard-coded and won’t change.

In case I add any column to the left of the dataset, the formula would count 3 columns from the beginning of the worksheet and not from the beginning of the dataset.

So, if you have a fixed dataset and need a simple formula, this will work fine.

But if you need this to be more fool-proof, use the one covered in the next section.

Lookup And Return Cell Address Using the CELL Function

While the ADDRESS function was made specifically to give you the cell reference of the specified row and column number, there is another function that also does this.

It’s called the CELL function (and it can give you a lot more information about the cell than the ADDRESS function).

Below is the syntax of the CELL function:

=CELL(info_type, [reference])

where:

info_type: the information about the cell you want. This could be the address, the column number, the file name, etc.
[reference]: Optional argument where you can specify the cell reference for which you need the cell information.

Now, let’s see an example where you can use this function to look up and get the cell reference.

Suppose you have a dataset as shown below, and you want to quickly know the cell address that contains the department for employee id KR256.

Below is the formula that will do this:

=CELL("address", INDEX($A$1:$D$20,MATCH("KR256",$A$1:$A$20,0),3))

The above formula is quite straightforward.

I have used the INDEX formula as the second argument to get the department for the employee id KR256.

And then simply wrapped it within the CELL function and asked it to return the cell address of this value that I get from the INDEX formula.

Now here is the secret to why it works – the INDEX formula returns the lookup value when you give it all the necessary arguments. But at the same time, it would also return the cell reference of that resulting cell.

In our example, the INDEX formula returns “Sales” as the resulting value, but at the same time, you can also use it to give you the cell reference of that value instead of the value itself.

Normally, when you enter the INDEX formula in a cell, it returns the value because that is what it’s expected to do. But in scenarios where a cell reference is required, the INDEX formula will give you the cell reference.

In this example, that’s exactly what it does.

And the best part about using this formula is that it is not tied to the first cell in the worksheet. This means that you can select any data set (which could be anywhere in the worksheet), use the INDEX formula to do a regular look up and it would still give you the correct address.

And if you insert an additional row or column, the formula would adjust accordingly to give you the correct cell address.

So these are two simple formulas that you can use to look up and find and return the cell address instead of the value in Excel.

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

What Do You Mean By Cell Reference in Excel?

A cell reference in Excel refers to other cells to a cell to use its values or properties. So in simple terms, if we have data in some random cell A2 and we want to use that value of cell A2 in cell A1, we can use =A2 in cell A1. So it will copy the value of A2 in A1. So it is called cell referencing in Excel.
For example, suppose you insert C1O. As a result, it will expand “Column C” and the “10th Row.” Likewise, we can also define or declare cell references to any location in the worksheet. We may also activate another way for cell reference, e.g., R7C7 from Excel “Options,” where R7 is Row 7 and C7 is Column 7.

Explained

Excel worksheet is made up of cells. Each cell has a cell reference
Cell reference contains one or more letters or alphabet followed by a number where the letter or alphabet indicates the column and the number represents the row.
Each cell can be located or identified by its cell reference or address, e.g., B5
Each cell in an Excel worksheet has a unique address. The address of each cell is defined by its location on the grid. g. In the below-mentioned screenshot, the address “B5” refers to the cell in the fifth row of column B

Even if we enter the cell address directly in the grid or name window, it will go to that cell location in the worksheet. Cell references can refer to either one cell, a range of cells, or even entire rows and columns.

When a cell reference refers to more than one cell, it is called “range.” E.g., A1:A8 indicates the first 8 cells in column A. In between, a colon is used.

Types of Cell Reference in Excel

Relative cell references: It does not contain dollar signs in a row or column, e.g., A2. Relative cell reference type in excelIn Excel, relative references are a type of cell reference that changes when the same formula is copied to different cells or worksheets. Let's say we have =B1+C1 in cell A1, and we copy this formula to cell B2 and it becomes C2+D2.read more changes when a formula is copied or dragged to another cell. In Excel, cell referencing is relative by default. It is the most commonly used cell reference in the formula.
Absolute cell references: Absolute Cell Reference Absolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read morecontains dollar signs attached to each letter or number in a reference, e.g., $B$4. Suppose we mention a dollar sign before the column and row identifiers. It makes absolute or locks both the column and the row, i.e., where cell reference remains constant even if it is copied or dragged to another cell.
Mixed cell references in Excel: In Excel, mixed cell references contain dollar signs attached to either the letter or the number in a reference. E.g., $B2 or B$4. It is a combination of relative and absolute references.

Now let us discuss each of the cell references in detail –

#1 How to Use Relative Cell Reference?

The below-mentioned pharma sales table below contains medicine “Products” in column C (C10:C16), “Quantity Sold” in column D (D10:D16), and “Total_Sales_Value” in column F, which we need to find out.

To calculate the total sales for each item, we need to multiply the price of each item by its quantity of that.

Let us check out for the first item; for the first item, the formula in cell F10 would be multiplication in excelMultiplication in excel is performed by entering the comparison operator “equal to” (=), followed by the first number, the “asterisk” (*), and the second number.read more – D10*E10.

It returns the total sales value.

Now, instead of entering the formula for all the cells, we can apply a formula to the entire range. To copy the formula down the column, click inside cell F10, and we can see that the cell is selected. Then, select the cells till F16. So, that column range will get selected. Then, we will click “Ctrl+D” to apply the formula to the entire range.

Here, when you copy or move a formula with a relative cell reference to another row. Automatically, row references will change (similarly for columns also)

We can notice that the cell reference automatically adjusts to the corresponding row.

To check a relative reference, we must select any of the cells of the “Total _Sales_ Value” in column F, and we can view the formula in the formula bar. E.g., In cell F14, we can observe that the formula has been changed from D10*E10 to D14*E14.

#2 How to Use Absolute Cell Reference?

The below-mentioned pharma product table contains medicine “Products” in column H (H6:H12), and it’s “Old_Price” in column I (I6:I12), and “New_Price” in column J, which we need to find out with the help of absolute cell reference.

We can see that the rate for each product is increased by 5% effective from Jan 2019 and is listed in cell “K3”.

To calculate the “New_Price” for each item, we need to multiply the old price of each item by the percentage price increase (5%) and add the “Old_Price” to it.

Let us check out the first item. For the first item, the formula in cell J6 would be =I6*$K$3+I6, where it returns the new price.

Here, the percentage rate increase for each product is 5%, a common factor. Therefore, we must add a dollar symbol in front of the row and column number for the cell “K3” to make it an absolute reference, i.e., $K$3.Wecan add it by clicking the “function+F4” key once.

Here the dollar sign for the cell “K3” fixes the reference to a given cell, where it remains unchanged no matter when you copy or apply a formula to other cells.

Here, $K$3 is an absolute cell reference, whereas “I6” is a relative cell reference. It changes when you apply to the next cell.

Now, instead of entering the formula for all the cells, we can apply a formula to the entire range. To copy the formula down the column, click inside cell J6, and we will see that the cell has been selected. Then, we must select the cells till J12. So, that column range will get selected. Then click “Ctrl+D,” so the formula is applied to the entire range.

#3 How to Use Mixed Cell Reference?

In the below-mentioned table, we have values in each row (D22, D23, and D24) and columns (E21, F21, and G21). Here, we have to multiply each column with each row with the help of a mixed cell reference.

There are two types of mixed cell references that we can use here to get the desired output.

Let us apply two types of mixed references below in the cell “E22.”

The formula would be =$D22*E$21

#1 – $D22: Absolute column and Relative row

Here, the dollar sign before column D indicatesthat only the row number can change. At the same time, the column letter D is fixed. It does not change.

When we copy the formula to the right side, the reference will not change because it is locked, but When you copy it down, the row number will change because it is not locked.

#2 – E$21: Absolute row and Relative column

The dollar sign right before the row number indicates only the column letter E can change. Whereas the row number is fixed; it does not change.

The row number will not change when copying the formula because it is locked. But when we copy the formula to the right side, the column alphabet will change because it is not locked.

Now, instead of entering the formula for all the cells, we can apply a formula to the entire range. Now, we will click inside cell E22. As a result, first, we will see that the cell is selected. Then, we will select the cells until G24 so that the entire range will get selected. Next, we will click on the “Ctrl+D” key first and later “Ctrl+R.”