How to Return Cell Address Instead of Value in Excel

Sumit Bansal
Written by

Sumit Bansal is the founder of TrumpExcel.com and a 13-time Microsoft Excel MVP. He started this site in 2013 to share his passion for Excel through easy tutorials, tips, and training videos, helping you master Excel, boost productivity, and maybe even enjoy spreadsheets!

Last updated

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. A cell address is the column letter and row number of a cell, such as C6.

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 are 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.

I’ll also show you how to get the address of every matching cell when the value shows up more than once.

Follow along with the example file

Return Cell Address Instead of Value Excel.xlsx

Download

Lookup and Return Cell Address Using CELL and XLOOKUP

Let’s start with the shortest formula, which works in Excel 365, 2024, and 2021.

The ADDRESS function was made specifically to give you a cell address (I cover it in the next section), but 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.

Below is a list of employees with their ID, name, department, and office, and I want the address of the cell that holds the department for the employee ID in cell G1 (KR256) in cell G2.

Employee list with ID, name, department and office, and the employee ID KR256 entered in cell G1

Below is the formula that will do this:

=CELL("address",XLOOKUP(G1,A2:A11,C2:C11))
CELL and XLOOKUP formula in G2 returning the cell address $C$6 for employee ID KR256

The above formula is quite straightforward.

How this formula works:

  • XLOOKUP(G1,A2:A11,C2:C11) – This looks for KR256 in A2:A11 and returns the matching cell from the Department column, which is C6 (it holds “Sales”).
  • CELL(“address”, …) – This gives you the address of that cell as text, which is $C$6.

Now here is the secret to why it works.

The XLOOKUP 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 XLOOKUP 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 XLOOKUP 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 XLOOKUP formula will give you the cell reference.

In this example, that’s exactly what it does. The second argument of CELL needs a cell reference, so XLOOKUP hands it the cell C6 instead of the value “Sales”.

This is also why the trick doesn’t work with VLOOKUP. VLOOKUP can only return a value, so Excel won’t even accept a formula like =CELL(“address”,VLOOKUP(…)).

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 XLOOKUP to do a regular lookup, 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.

That’s not true for every method. The simple ADDRESS formula in the next section counts positions from row 1, so it gives the wrong cell when your data starts lower down or when you insert rows above it.

If your data is on a different sheet, the result also includes the file and sheet name, such as [Book1.xlsx]Data!$C$6.

If you plan to copy this formula down a long list, keep in mind that Microsoft lists CELL among the functions that can recalculate every time anything in the workbook changes.

With a few lookups, you won’t notice it. But with thousands of these formulas, every edit makes Excel recalculate all of them, which can slow the workbook down, so use the ADDRESS method in the next section instead.

If you are using Excel 2019 or earlier, use the below formula instead (it’s in cell G3 of the example file):

=CELL("address",INDEX(C2:C11,MATCH(G1,A2:A11,0)))

Here, the MATCH function finds the position of KR256 in A2:A11, and I have used the INDEX formula 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. INDEX returns a cell reference just like XLOOKUP does, so you get the same $C$6.

Sometimes you don’t need the full address, just the row number where the match is. In that case, wrap the same XLOOKUP in the ROW function (this one is in cell G4 of the example file):

=ROW(XLOOKUP(G1,A2:A11,C2:C11))
ROW and XLOOKUP formula in G4 returning row number 6 for employee ID KR256

This returns 6, which is the row of the worksheet where KR256 sits. Since XLOOKUP hands ROW the actual cell, you get the real row number even if your data doesn’t start in row 1.

If you are using Excel 2019 or earlier, enter the below formula in cell G4 instead:

=ROW(INDEX(C2:C11,MATCH(G1,A2:A11,0)))

Lookup and Return Cell Address Using the ADDRESS Function

Now let’s look at the function that was built for this job.

The ADDRESS function in Excel is meant to do 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 you 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 a sheet name that you want to add along with the cell address (for example, Data!$C$6)

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

Below is the same list of employees with their ID, name, department, and office, and I want the address of the cell that holds the department for the employee ID in cell G1 (KR256) in cell G2.

Employee list with the employee ID KR256 in cell G1 and an empty cell for its address

Below is the formula that will do this:

=ADDRESS(MATCH(G1,A1:A11,0),3)
ADDRESS and MATCH formula in G2 returning $C$6 as the address of the department for KR256

In the above formula, I have used the MATCH function to find out the row number that contains the given employee ID. Since the range A1:A11 starts in row 1, the position MATCH returns (6) is also the row number.

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 a row above the dataset or a column to the left of the dataset. Instead of showing an error, it quietly keeps showing the old address.

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.

The row has the same problem. MATCH counts from the top of the range A1:A11, so once a row is added above the data, that position is no longer the row number.

You can see this in the example file. Insert a row above the data and the formula still says $C$6, even though the department for KR256 has moved down to C7.

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 below formula instead:

=ADDRESS(MATCH(G1,A:A,0),COLUMN(C:C))
Insert-proof ADDRESS formula with whole-column MATCH and COLUMN returning $C$6 in G3

How this formula works:

  • MATCH(G1,A:A,0) – This looks for KR256 in the whole of column A. Since a whole column always starts at row 1, the position it returns (6) is always the actual row number, even after you insert rows above the data.
  • COLUMN(C:C) – This returns 3, the column number of column C. If you insert a column to the left, the reference moves to D:D and this becomes 4 on its own.
  • ADDRESS – This turns row 6 and column 3 into $C$6.

Since this formula searches the whole of column A, make sure the employee ID doesn’t appear anywhere else in that column. If it does, MATCH stops at the first one it finds.

By default, ADDRESS gives you an absolute reference with dollar signs ($C$6). If you want a plain C6, use 4 as the third argument:

=ADDRESS(MATCH(G1,A:A,0),COLUMN(C:C),4)
ADDRESS formula with 4 as the third argument returning the relative address C6 in G4

The ADDRESS and MATCH functions work in all Excel versions, so this method is a good pick if your file needs to work in an older version.

Return the Cell Address of Every Match (Not Just the First)

Both the methods above stop at the first match, so if the value you’re looking for shows up more than once, you only get the first address.

Below is the same list of employees, and I want the addresses of all the cells in the Department column that say Sales (the department is typed in cell G1), listed in column I.

Employee list with Sales entered in cell G1 and an empty Cell Addresses column

Here is the formula that will do this:

=ADDRESS(FILTER(ROW(C2:C11),C2:C11=G1),COLUMN(C2),4)
ADDRESS and FILTER formula spilling C3, C6 and C9, the cells in the Department column that say Sales

How this formula works:

  • ROW(C2:C11) – This gives the row numbers of the Department column, 2 to 11.
  • FILTER(ROW(C2:C11),C2:C11=G1) – This keeps only the row numbers where the department is Sales, which are 3, 6, and 9.
  • COLUMN(C2) – This returns 3, the column number of the Department column.
  • ADDRESS(…,4) – This turns each row number and column 3 into an address, and the 4 makes them plain addresses without dollar signs.

The result is C3, C6, and C9, and the formula spills down on its own, so you don’t need to copy it.

If nothing matches (say G1 has a department that isn’t in the list), FILTER returns a #CALC! error. To show a friendlier message instead, wrap the formula in the IFERROR function:

=IFERROR(ADDRESS(FILTER(ROW(C2:C11),C2:C11=G1),COLUMN(C2),4),"Not found")

This formula needs the FILTER function, which is available in Excel 365, 2024, and 2021.

If you are using Excel 2019, enter the below formula in cell I2 instead and confirm it with Ctrl + Shift + Enter. It uses the TEXTJOIN function to list all the addresses in one cell, separated by commas:

=TEXTJOIN(", ",TRUE,IF(C2:C11=G1,ADDRESS(ROW(C2:C11),COLUMN(C2),4),""))

Find the Cell Address of a Value Anywhere in a Table

The formula above searches one column. But sometimes the value you’re looking for could be in any cell of a table, and you want to know every place it shows up.

Below is a weekly shift roster, and I want the addresses of all the shifts that Meredith (the name in cell G1) is working, listed in column I.

Weekly shift roster with morning, afternoon and evening shifts, and the name Meredith entered in cell G1

Here is the formula that will do this:

=TOCOL(IF(B2:D8=G1,ADDRESS(ROW(B2:D8),COLUMN(B2:D8),4),NA()),2)
TOCOL, IF and ADDRESS formula spilling B2, D3, C5 and B7, every shift Meredith is working

How this formula works:

  • ADDRESS(ROW(B2:D8),COLUMN(B2:D8),4) – ROW gives the row numbers 2 to 8 and COLUMN gives the column numbers 2 to 4. ADDRESS pairs every row number with every column number, so you get a grid of addresses (B2, C2, D2, and so on) the same shape as the roster.
  • IF(B2:D8=G1,…,NA()) – This keeps the address only where the cell has Meredith, and puts a #N/A error everywhere else.
  • TOCOL(…,2) – This turns the grid of results into a single column, and the 2 tells it to skip the errors. So only Meredith’s addresses are left.

The result is B2, D3, C5, and B7, listed row by row. I’ve used the same trick to list every blank cell in a range in my ADDRESS function tutorial.

This formula needs the TOCOL function, which is only available in Excel 365 and Excel 2024.

If nothing matches, it returns a #CALC! error, and you can wrap it in IFERROR just like the previous formula.

If you are using Excel 2021 or 2019, enter the below formula in cell I2 instead (in Excel 2019, confirm it with Ctrl + Shift + Enter). It lists all the addresses in one cell, separated by commas:

=TEXTJOIN(", ",TRUE,IF(B2:D8=G1,ADDRESS(ROW(B2:D8),COLUMN(B2:D8),4),""))

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

If you want to take this further, my CELL function tutorial also shows how to get the address of the highest value and how to build a link that jumps straight to a lookup result.

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

Sumit Bansal

Sumit Bansal

13x Microsoft Excel MVP

Hey! I'm Sumit Bansal, founder of trumpexcel.com and a Microsoft Excel MVP. I started this site in 2013 because I genuinely love Microsoft Excel (yes, really!) and wanted to share that passion through easy Excel tutorials, tips, and Excel training videos. My goal is straightforward: help you master Excel skills so you can work smarter, boost productivity, and maybe even enjoy spreadsheets along the way!

Leave a Comment

Get the FREE 51 Excel Tips Ebook

Enter your details and the free PDF is on its way to your inbox.

Hmm, that didn't go through. Please check your email and try again.

No spam. You'll also get my weekly Excel newsletter. Unsubscribe anytime.

Check your inbox!

The ebook is on its way to your email. It usually lands within a couple of minutes.