+ Reply to Thread
Results 1 to 16 of 16

Finding Certain Cell Value and Returning the Cell Address

  1. 02-09-2013, 02:06 AM #1
    meacoldinch
    meacoldinch is offline
    Registered User
    Join Date
    02-09-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2011
    Posts
    4

    Finding Certain Cell Value and Returning the Cell Address

    Ok! So I have a bit of a conundrum for you all.

    I am attempting to find a formula that will first locate a certain cell value within a range (the FIND formula), but then instead of returning a value such as 1 once the desired cell value is located, it will instead give me the cell address of where the desired cell value resides.

    The Find formula will tell me if the cell value exists within the range, but I need something more than that. I played around with the ADDRESS formula and the SEARCH formula, and combinations therefore, but to no avail.

    Any suggestions?
    Reply With QuoteRegister To Reply
  2. 02-09-2013, 02:45 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Finding Certain Cell Value and Returning the Cell Address

    Hi and welcome to the forum

    what do you intend to do with the cell ref once you have it?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 02-09-2013, 02:48 AM #3
    meacoldinch
    meacoldinch is offline
    Registered User
    Join Date
    02-09-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Finding Certain Cell Value and Returning the Cell Address

    Nothing, I just want to find the certain value, and where exactly it is located in the overall spreadsheet
    Reply With QuoteRegister To Reply
  4. 02-09-2013, 02:59 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Finding Certain Cell Value and Returning the Cell Address

    ok, try something like this. you will have to adjust the references/ranges to suite...
    =CELL("address",INDEX(H15:H19,MATCH(17.04,H15:H19,0),1))
    Reply With QuoteRegister To Reply
  5. 02-10-2013, 04:02 PM #5
    meacoldinch
    meacoldinch is offline
    Registered User
    Join Date
    02-09-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Finding Certain Cell Value and Returning the Cell Address

    Hm.... That didn't seem to work. It only returned the #VALUE! result.

    Any more ideas what is wrong with it?
    Reply With QuoteRegister To Reply
  6. 02-10-2013, 05:06 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Finding Certain Cell Value and Returning the Cell Address

    Try this. Adjust to suit your range. This formula is for the range F5:N14. These are array formulae so enter with Ctrl+Shift+Enter

    =ADDRESS(MIN(IF(F5:N14=C1, ROW(F5:N14))), MIN(IF(F5:N14=C1, COLUMN(F5:N14))))

    Enter the number that you want the address of in C1 and the address will be given with an Absolute Reference.

    =ADDRESS(MIN(IF(F5:N14=C1, ROW(F5:N14))), MIN(IF(F5:N14=C1, COLUMN(F5:N14))),4)

    This formula will give the address with a Relative cell reference.
    Reply With QuoteRegister To Reply
  7. 02-10-2013, 10:48 PM #7
    meacoldinch
    meacoldinch is offline
    Registered User
    Join Date
    02-09-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Finding Certain Cell Value and Returning the Cell Address

    That did work, however it only works for numerical entries. If I try to use letters (which is what I am trying to find), it comes up with a #NAME!. Is there any way to fix that?
    Reply With QuoteRegister To Reply
  8. 02-10-2013, 11:00 PM #8
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Finding Certain Cell Value and Returning the Cell Address

    can you try this one.
    B1 is the value to be seacrhed
    A1 to A100 the list of numbers or text

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  9. 02-11-2013, 11:53 AM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Finding Certain Cell Value and Returning the Cell Address

    The above formulae will find text IF and only IF the text in C1 matches exactly the text in the range that you are searching. This will not pick specific text out of a string of text within the range of cells.
    Last edited by newdoverman; 02-11-2013 at 11:56 AM.
    Reply With QuoteRegister To Reply
  10. 02-11-2013, 07:45 PM #10
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Finding Certain Cell Value and Returning the Cell Address

    yup then add wildcard in the match.
    Reply With QuoteRegister To Reply
  11. 01-03-2014, 06:13 PM #11
    frozo
    frozo is offline
    Registered User
    Join Date
    01-03-2014
    Location
    Qatar
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Finding Certain Cell Value and Returning the Cell Address

    I want to extract the number coming after each Asterisk and return to a certain cell
    2*5+3*6+4*9+5*4 is coming in one long cell as an output of a software report
    Simply, If [2*5+3*6+4*9+5*4] is in cell A1, how can I tell excel to get "5" in cell A2 and "3" in cell A3 and "4" in cell A4
    Reply With QuoteRegister To Reply
  12. 01-03-2014, 11:03 PM #12
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Finding Certain Cell Value and Returning the Cell Address

    Quote Originally Posted by frozo View Post
    I want to...
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  13. 01-03-2014, 11:09 PM #13
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Finding Certain Cell Value and Returning the Cell Address

    Here is a nice UDF for this

    Please Login or Register  to view this content.
    User formula:

    =GetAddress(Value,range)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  14. 12-15-2014, 01:45 PM #14
    Willis Copeland
    Willis Copeland is offline
    Registered User
    Join Date
    12-15-2014
    Location
    santa barbara
    MS-Off Ver
    Excell 2011
    Posts
    2

    Re: Finding Certain Cell Value and Returning the Cell Address

    Question: I have a column (A1:A10) of peoples names and a column (B1:B10) of numbers. I want to put into cell C1 a function that will locate the cell in B1:B10 that contains the smallest number and then display the person’s name from A1:A10 that is located just to the left of that number. Any help would be appreciated.
    Reply With QuoteRegister To Reply
  15. 12-15-2014, 01:54 PM #15
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Finding Certain Cell Value and Returning the Cell Address

    @Willis Copeland

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Reply With QuoteRegister To Reply
  16. 12-15-2014, 01:55 PM #16
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Finding Certain Cell Value and Returning the Cell Address

    @ moderator

    Please delete.

    Posted too late.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1