# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Find cell address within a table?

## Sean Anderson

Hello,
Does anyone know the formula that produces the address (e.g. $A$1) of a cell within a table? I found the formula that gives the address of a single cell within a single row (or the address of a single cell within a single column). But I need the formula that gives the address of a cell where the row intersects the column. If I know the column header name and the row header name, how do I find the cell address for the two dimensional intersection? Thanks a billion.

----------


## VBA Noob

Does this help

http://www.contextures.com/xlFunctions03.html

VBA Noob

----------


## Teethless mama

> Hello,
> Does anyone know the formula that produces the address (e.g. $A$1) of a cell within a table? I found the formula that gives the address of a single cell within a single row (or the address of a single cell within a single column). But I need the formula that gives the address of a cell where the row intersects the column. If I know the column header name and the row header name, how do I find the cell address for the two dimensional intersection? Thanks a billion.



=ADDRESS(MATCH("mama",A1:A10,0),1,1)

or

="A"&MATCH("mama",A1:A10,0)

----------


## Sean Anderson

Thanks for the specs. They were useful. However, it appears that MATCH/INDEX funtions only give the value of a cell rather than the address of the cell. For example, Let's take the following list.

........Jan Feb March
Blue   50     25     60
Red   20     30      05

Somebody asks, "How much is Red in March?" 
To answer this question (05), I can use the match/index functions that you recommended.

However, I don't want the value 05. Instead, I want the cell address. In this case it would be cell $D$3. I think the solution may be with the (=Address) function. My book offers the following formulas to lookup cell addresses.

=ADDRESS(ROW(Data)+MATCH(Target,Data,0)-1,COLUMN(DATA))...........This one is good for finding a cell address in a single column range.

OR

=ADDRESS(ROW(Data),COLUN(Data)+MATCH(Target,Data,0)-1).........This one is good for finding the cell address in a single row.

Any suggestions? Thanks much.

----------


## VBA Noob

The below returns $D$3

=ADDRESS(MATCH("Red",A1:A3,0),MATCH("March",A1:D1,0))

VBA Noob

----------


## Sean Anderson

Thanks VBA Noob, that works perfectly. You used an The ADDRESS function combined with two MATCH functions. You saved the day.

----------


## VBA Noob

Glad it helped

VBA Noob

----------


## daddylonglegs

Hello Sean,

Can I ask why you want the cell address?

Often people ask how to get a cell address and then, next question, how do I use that address in another function.....?

If that's your aim then ADDRESS function isn't normally the best approach, as you note above INDEX/MATCH returns a cell value.....BUT, when used within another function INDEX/MATCH does give a cell reference...

----------


## Sean Anderson

Here's a follow up question. Can you help?

Let's say I have the following table. There are no row or column headers.

[yellow]  [dog]  [75]
[871   ]  [blue]  [car]
[123]     [rain]   [cat]

How do I find the cell address of [blue]? All I know is that the range of cells is
A1:C3. These values change every day, and I can't add or delete rows or columns to the table. The table has no row or column headers.

I'm new at this, so please bear with me. Thanks a mil.

----------


## Sean Anderson

Hello,

To answer your question, Daddylonglegs,

I had a theory on how to ultimately find the sum of a range of cells (see my other thread "Advanced use of Address function" on 03/03/07. I didn't mention my master plan because I'm attacking my problem in small pieces. It turned out that I didn't need the Address function like you said. Excel is like a jigsaw puzzle. I need to learn how to fit all the pieces together to build useful megaformulas. Thanks for your reply.

I posted another question about the Adress function ("How to find address of any cell within a range without knowing the row or column header.") just now to this thread. I have another idea I'm trying to solve. Can you help with that question?

----------


## daddylonglegs

Again, if you're trying to find the address as part of a larger exercise then I'd suggest that you probably wouldn't need the address function at all ( as I think you found with your other thread).

Sometimes it can be better to break these problems down into smaller sub-problems but in all the lookup/summing type problems I've seen I can't recall one which can't be more efficiently solved with functions other than ADDRESS.

Having said that, for the above, assuming "blue" will only ever occur once in the range

=ADDRESS(MIN(IF(A1:C3="blue",ROW(A1:C3))),MIN(IF(A1:C3="blue",COLUMN(A1:C3))))

confirmed with CTRL+SHIFT+ENTER

----------


## emm

> daddylonglegs
> 
> =ADDRESS(MIN(IF(A1:C3="blue",ROW(A1:C3))),MIN(IF(A1:C3="blue",COLUMN(A1:C3))))
> 
> confirmed with CTRL+SHIFT+ENTER




Been looking the web alnight for this formula.  Have a complicated linking problem that this may help with...

----------


## abz

> Again, if you're trying to find the address as part of a larger exercise then I'd suggest that you probably wouldn't need the address function at all ( as I think you found with your other thread).
> 
> Sometimes it can be better to break these problems down into smaller sub-problems but in all the lookup/summing type problems I've seen I can't recall one which can't be more efficiently solved with functions other than ADDRESS.
> 
> Having said that, for the above, assuming "blue" will only ever occur once in the range
> 
> =ADDRESS(MIN(IF(A1:C3="blue",ROW(A1:C3))),MIN(IF(A1:C3="blue",COLUMN(A1:C3))))
> 
> confirmed with CTRL+SHIFT+ENTER




HOw can you solve the problem, if say blue is occuring multiple times in the array.

Thanks

----------


## NBVC

abz,

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------


## daddylonglegs

Hello abz,

Could you please start your own thread with a description of your specific problem and what you want to do. A small example (or examples) often help substantially.....

----------


## NBVC

See here, dll....

http://www.excelforum.com/excel-prog...n-a-table.html

----------


## mhrobson

> Again, if you're trying to find the address as part of a larger exercise then I'd suggest that you probably wouldn't need the address function at all ( as I think you found with your other thread).
> 
> Sometimes it can be better to break these problems down into smaller sub-problems but in all the lookup/summing type problems I've seen I can't recall one which can't be more efficiently solved with functions other than ADDRESS.
> 
> Having said that, for the above, assuming "blue" will only ever occur once in the range
> 
> =ADDRESS(MIN(IF(A1:C3="blue",ROW(A1:C3))),MIN(IF(A1:C3="blue",COLUMN(A1:C3))))
> 
> confirmed with CTRL+SHIFT+ENTER



Can "blue" be substituted with a value from another cell in this case? Say you want the value in A10, which is dependent on other cells, can you put in ...(A1:C3=A10,.....?

----------


## shg

mhrobson, please read post #14 in this thread.

----------

