Excel's ADDRESS function is easy to misunderstand because it does not return the value in a cell. It returns the cell's location as text. That distinction matters when you are building dynamic reports, showing users where a result came from, or combining row and column positions from lookup formulas.
Once you understand the difference between an address such as $B$5 and an actual cell reference, ADDRESS becomes a practical tool for locating matches, constructing references, and working with dynamic worksheet coordinates.
What the Excel ADDRESS Function Returns
The Excel ADDRESS function creates a cell reference as text from a row number and column number. For example, =ADDRESS(5,2) returns $B$5.
The important word is text. Excel displays the result as an address, but it is not automatically a usable reference. If cell B5 contains 250, =ADDRESS(5,2) returns $B$5, not 250.
When you need Excel to retrieve the value at a constructed address, wrap ADDRESS in INDIRECT:
=INDIRECT(ADDRESS(5,2))
That formula evaluates the text $B$5 as a reference and returns the value stored in B5. Use this combination carefully, though: in many cases, INDEX is a clearer and more efficient way to return a value.
ADDRESS syntax and arguments
The full syntax is:
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
- row_num: The worksheet row number. For example, 5 represents row 5.
- column_num: The worksheet column number. Column A is 1, B is 2, Z is 26, and AA is 27.
- abs_num: Optional. Controls whether row and column references use dollar signs.
- a1: Optional. TRUE returns A1-style references, which is Excel's default. FALSE returns R1C1-style references.
- sheet_text: Optional. Adds a sheet name to the returned address.
Row and column inputs must be positive whole numbers within Excel's worksheet limits. If you pass zero, a negative number, or an invalid column number, ADDRESS returns an error.
Build Your First ADDRESS Formula
Start with the simplest possible formula:
=ADDRESS(5,2)
Enter it in any empty cell and press Enter. Excel returns $B$5 because row 5 is row 5 and column 2 is column B.
The dollar signs are expected. By default, ADDRESS uses an absolute reference, meaning both the column and row are locked. This is the same reference style Excel creates when you press F4 while editing a normal cell reference.
Try a few variations to see the number-to-column relationship:
- =ADDRESS(1,1) returns $A$1
- =ADDRESS(10,3) returns $C$10
- =ADDRESS(25,27) returns $AA$25
This basic Excel formula ADDRESS example is useful on its own when you need to display a cell location in a report, audit sheet, or error message.
Control Absolute, Relative, and Mixed References
The third ADDRESS argument, abs_num, controls the dollar signs in the returned address. This is important when the address will be displayed to another user or passed into a formula such as INDIRECT.
- 1 returns an absolute row and column reference: $B$5
- 2 returns a relative column and absolute row reference: B$5
- 3 returns an absolute column and relative row reference: $B5
- 4 returns a fully relative reference: B5
For example, these formulas all use row 5 and column 2:
- =ADDRESS(5,2,1) returns $B$5
- =ADDRESS(5,2,2) returns B$5
- =ADDRESS(5,2,3) returns $B5
- =ADDRESS(5,2,4) returns B5
Use absolute references when the constructed location should remain fixed. Use relative or mixed references only when you deliberately want the result to shift based on where a formula is copied. A common mistake is leaving out abs_num and then wondering why the returned address contains dollar signs.
Create a Dynamic Address From Cells
ADDRESS becomes more useful when row and column values come from worksheet cells rather than being typed directly into the formula. If A2 contains 12 and B2 contains 4, use:
=ADDRESS(A2,B2)
The result is $D$12. Excel reads the values in A2 and B2, then converts the numeric coordinates into a cell address.
This is a straightforward way to create an Excel formula address from another cell. For instance, a reporting sheet might store a selected row in A2 and calculate the correct report column in B2. ADDRESS can show exactly which cell those selections point to.
You can also calculate the column number. Suppose row 1 contains month headers in B1:M1 and cell D2 contains a selected month name. This formula finds the selected header's position:
=MATCH(D2,B1:M1,0)
Because that MATCH result is relative to B1:M1 rather than the full worksheet, you would normally add the range offset before using it as an Excel column number. A simple approach is:
=COLUMN(B1)-1+MATCH(D2,B1:M1,0)
You can then feed that result into ADDRESS. Even when inputs are dynamic, remember that ADDRESS still returns text.
Turn a column number into a column letter
If B2 contains a column number, this formula returns an address with a relative reference style:
=ADDRESS(1,B2,4)
For example, if B2 contains 28, the result is AB1. To extract only the column letter, remove the final row number:
=SUBSTITUTE(ADDRESS(1,B2,4),"1","")
This works for normal column-letter needs because the row is fixed at 1. Still, do not overuse text manipulation just to force formulas into letters. If the real goal is to return a value from a table, INDEX is usually clearer than converting numbers to letters and building a text reference.
Use ADDRESS With INDIRECT to Return a Cell Value
The core pattern for evaluating a constructed address is:
=INDIRECT(ADDRESS(row_num,column_num))
ADDRESS creates the text address, and INDIRECT turns that text into a working reference. For example, if E2 contains 8 and F2 contains 3, the formula below retrieves the value in C8:
=INDIRECT(ADDRESS(E2,F2))
This can be useful for a small lookup grid where users select a row and column number. The formula lets those two selections point to a cell without manually editing the reference.
Example: retrieve a value at a selected row and column
Imagine a worksheet where a manager enters a row number in E2 and a column number in F2. E2 contains 8 and F2 contains 3. The calculation happens in two stages:
- =ADDRESS(E2,F2) returns $C$8.
- =INDIRECT(ADDRESS(E2,F2)) returns the value currently held in C8.
By default, the generated address is absolute. To build a relative address instead, use =INDIRECT(ADDRESS(E2,F2,4)). In many worksheets the returned value will be the same, but reference style can matter if the formula is copied or if you display the address separately.
Warning: INDIRECT is volatile. Excel recalculates it frequently, and formulas built with INDIRECT are harder to trace because the referenced cell is created as text. In a large workbook, that can hurt performance and make auditing more difficult. If your real goal is a direct row-and-column lookup, prefer:
=INDEX(A1:Z100,E2,F2)
INDEX returns the value at the selected position without constructing text or using a volatile function.
Add a Sheet Name to an ADDRESS Formula
The optional fifth argument, sheet_text, adds a worksheet name to the reference. For example:
=ADDRESS(5,2,1,TRUE,"Sales Data")
This returns:
'Sales Data'!$B$5
Excel adds apostrophes because the sheet name contains a space. Letting ADDRESS build the result is safer than manually combining a sheet name, exclamation point, quotes, and a cell reference in a long text formula.
You can also take the sheet name from a cell. If A1 contains Sales Data, use:
=ADDRESS(5,2,1,TRUE,A1)
This supports a controlled dynamic-sheet workflow, such as selecting a department sheet from a dropdown. If you need the value from that address, wrap the formula in INDIRECT:
=INDIRECT(ADDRESS(5,2,1,TRUE,A1))
Keep the sheet names consistent. A typo, an extra trailing space, or a renamed worksheet will make the constructed reference fail.
Return the Cell Address of a MATCH
MATCH finds a position, not a worksheet address. ADDRESS can convert that position into a visible location when you want Excel to return the cell address of a match.
Suppose product codes are listed in A1:A20 and you want to find the cell containing the code entered in D2. If the list begins in row 1, use:
=ADDRESS(MATCH(D2,A1:A20,0),COLUMN(A1:A20))
If D2 contains the value found in A7, MATCH returns 7. COLUMN(A1:A20) returns 1. ADDRESS then returns $A$7.
The range-start detail matters. MATCH returns a position within the lookup range, not always the actual worksheet row number. If your lookup list is A5:A20, a match in A7 has position 3, not 7. This formula correctly adjusts for that offset:
=ADDRESS(ROW(A5)-1+MATCH(D2,A5:A20,0),COLUMN(A5))
For more examples of using ROW to number rows and build references, see the Google Sheets ROW Formula: Number Rows and Build References guide.
That is the correct approach for returning the cell location rather than accidentally reporting a relative position as a worksheet row.
Return the full address of a matched value in a table
A robust pattern uses INDEX to identify the actual matched cell, then ROW and COLUMN to retrieve its worksheet coordinates:
=ADDRESS(ROW(INDEX(A5:A20,MATCH(D2,A5:A20,0))),COLUMN(A5))
If the matching item is in A7, INDEX returns a reference to A7, ROW returns 7, COLUMN returns 1, and ADDRESS returns $A$7.
Use this kind of formula when the address itself is required for reporting, navigation instructions, or error checks. If you simply need the matched item's associated value, use XLOOKUP, INDEX/MATCH, or another direct lookup instead. If you need to test whether text contains a particular value before building a reference, see Excel Contains Formula: Test Text and Return Matching Values.
Find a Cell Location Based on a Value in Two Dimensions
To find a cell location in a matrix, use one MATCH for the row label and another for the column header. Then convert both relative positions into true worksheet coordinates before passing them to ADDRESS.
Assume row labels are in A2:A10, column headers are in B1:F1, and the data grid begins at B2. A selected row label is in H2, and a selected column header is in H3. Use:
=ADDRESS(ROW(B2)-1+MATCH(H2,A2:A10,0),COLUMN(B2)-1+MATCH(H3,B1:F1,0))
If H2 contains East and H3 contains April, Excel finds each position, applies the row and column offsets from B2, and returns the exact cell location at their intersection.
For the value at that intersection, INDEX is usually the better formula:
=INDEX(B2:F10,MATCH(H2,A2:A10,0),MATCH(H3,B1:F1,0))
Use ADDRESS when the location itself is the result you need. Use INDEX when the contents of that location are what matter.
Choose ADDRESS, CELL, INDEX, or XMATCH for the Job
These functions overlap just enough to cause confusion, but each has a distinct job.
| Function | Best use | Example purpose |
|---|---|---|
| ADDRESS | Construct a cell reference as text | Return $D$12 from row 12 and column 4 |
| CELL | Report information about an existing reference | =CELL("address",D12) returns the address of D12 |
| INDEX | Return a value or reference at a position | Return the sales value at a selected row and column |
| XMATCH | Find a position in a range | Find the position of a selected month header |
Use ADDRESS when you need to show or construct a location. Use =CELL("address",reference) when you already have a reference and need to report its address. Use INDEX when you need a nonvolatile way to return a value at a row and column. Use XMATCH when you need a position and have a modern version of Excel.
XMATCH is not available in older Excel versions. In those workbooks, MATCH remains the compatible choice.
Avoid Common ADDRESS Formula Errors
Most ADDRESS problems come from mixing up positions, actual worksheet coordinates, and values. Check these issues before rebuilding the entire formula.
- #VALUE! error: Verify that row and column numbers are positive whole numbers. Check that a MATCH formula found a result rather than returning an error.
- Unexpected dollar signs: ADDRESS defaults to abs_num 1. Specify 4 for a relative address, or 2 and 3 for mixed references.
- Address displayed instead of value: ADDRESS returns text. Use INDIRECT only if you truly need Excel to evaluate that text as a reference.
- Wrong row after MATCH: Add the starting-row offset when the lookup range does not start in row 1.
- Wrong reference style: Keep the optional a1 argument consistent. Most Excel users should leave it as TRUE or omit it entirely.
- Dynamic sheet reference fails: Check the sheet name cell for spelling differences, hidden spaces, and renamed tabs.
When a formula is complex, test each component separately. First verify MATCH or XMATCH. Then check ROW and COLUMN outputs. Next confirm that ADDRESS returns the intended text. Only after that should you add INDIRECT, if it is genuinely necessary.
Use ADDRESS When You Need to Show or Construct a Location
ADDRESS is best when the desired result is a cell location such as $B$5, not the value stored in that cell. Combine it with MATCH, ROW, and COLUMN when you need to identify a location dynamically, and use INDIRECT only when that constructed text must become an evaluated reference.
For ordinary value lookups, INDEX or XLOOKUP is usually the better choice. But when a worksheet needs to display, report, or build a dynamic cell address, ADDRESS is the function designed for the job. If you want to make a generated location clickable, the Excel HYPERLINK Formula: Create Clickable and Dynamic Links guide covers the related HYPERLINK function.
If you need help translating a plain-language spreadsheet task into an Excel or Google Sheets formula, FormulaBerry can generate, explain, and help correct formulas.
