Closed Thread
Results 1 to 18 of 18

Find cell address within a table?

  1. #1
    Forum Contributor
    Join Date
    03-03-2007
    Posts
    101

    Find cell address within a table?

    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.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Does this help

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

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211
    Quote Originally Posted by 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.
    =ADDRESS(MATCH("mama",A1:A10,0),1,1)

    or

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

  4. #4
    Forum Contributor
    Join Date
    03-03-2007
    Posts
    101

    Need Address, Not Value

    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.
    Last edited by Sean Anderson; 03-03-2007 at 05:00 PM.

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    The below returns $D$3

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

    VBA Noob

  6. #6
    Forum Contributor
    Join Date
    03-03-2007
    Posts
    101

    You solved my problem.

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

  7. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Glad it helped

    VBA Noob

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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...

  9. #9
    Forum Contributor
    Join Date
    03-03-2007
    Posts
    101

    How to find address of any cell without knowing the header row or column?

    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.

  10. #10
    Forum Contributor
    Join Date
    03-03-2007
    Posts
    101

    Reply to Daddylonglegs' question

    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?

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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

  12. #12
    Registered User
    Join Date
    04-14-2005
    Posts
    3
    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...
    Last edited by emm; 09-16-2008 at 10:55 PM.

  13. #13
    Registered User
    Join Date
    06-08-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Find cell address within a table?

    Quote Originally Posted by daddylonglegs View Post
    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

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find cell address within a table?

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  15. #15
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find cell address within a table?

    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.....
    Audere est facere

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find cell address within a table?


  17. #17
    Registered User
    Join Date
    07-13-2010
    Location
    Albuquerque
    MS-Off Ver
    Mac 2008
    Posts
    37

    Re: Find cell address within a table?

    Quote Originally Posted by daddylonglegs View Post
    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,.....?

  18. #18
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Find cell address within a table?

    mhrobson, please read post #14 in this thread.
    Entia non sunt multiplicanda sine necessitate

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1