+ Reply to Thread
Results 1 to 3 of 3

VLOOKUP to return Cell reference rather than Cell Contents.

  1. #1
    Registered User
    Join Date
    07-22-2009
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    VLOOKUP to return Cell reference rather than Cell Contents.

    Hi - has anyone successfully returned a cell's reference rather than its contents?
    I have the following formula:
    =VLOOKUP(A3,Sheet2!$A$1:$I$450,9,FALSE)
    This returns the value: 23

    I would like a formula that returns the sheet and Cell Reference that this resides within. In this instance, the sheet is Sheet2 and the cell is I9

    I have attempted to nest my VLOOKUP within a CELL function but Excel doesn't like this.

    Thanks for your help!
    Warren

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: VLOOKUP to return Cell reference rather than Cell Contents.

    First off, may sound odd but can we ask why ?

    To answer the question, perhaps:

    ="Sheet2!I"&MATCH($A3,Sheet2!$I$1:$I$450,0)

  3. #3
    Registered User
    Join Date
    07-22-2009
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: VLOOKUP to return Cell reference rather than Cell Contents.

    Thanks DonkeyOte, your formula is great.

    My reason for needing the cell reference rather than the cell value?

    Example Data:

    Assignment_Time Bounce? (0/1)
    04/06/2009 08:14:14
    04/06/2009 08:14:14 0
    04/06/2009 08:30:02 1
    04/06/2009 12:22:55 1
    04/06/2009 13:09:09 1
    04/06/2009 14:30:46 1
    09/06/2009 15:54:31 1
    5

    This is the final two columns of data.
    I have Subtotals enabled in Sheet2. My Lookup finds the Subtotal (in this instance it's 5) but, I'm keen to confirm the final date/time stamp (09/06/2009 15:54:31.
    I have adjusted your formula to now capture this.

    Quite possibly a very long way round but, it works.

    Thanks again!
    Warren
    Last edited by Wozza147; 07-22-2009 at 07:52 AM. Reason: Spaces and tabs lost.

+ Reply to 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