Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Assistance (97;SR2)

    I have a HLookup formula that returns the value of a date. What I would like to do is determine the cell reference of the returned value.

    Example: HLOOKUP(10/02/02,A1:E1,1,false) The returned value is 10/02/02.

    <table border=1><td></td><td>Column A</td><td>Column B</td><td>Column C</td><td>Column D</td><td>Column E</td><td>Row1</td><td>10/01/02</td><td>10/02/02</td><td>10/03/02</td><td>10/04/02</td><td>10/05/02</td></table>
    What would the formula be to return the cell reference? In this case, "B1".

    Thanks,
    John

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Assistance (97;SR2)

    Something like:

    =ADDRESS(1,MATCH(A5,A1:E1))

    Assumes the date you are looking for a date entered in Cell A5. The 1 in =ADDRESS(1, assumes that the dates you are searcing are in ROW 1. (A1:E1) Amend to suit.

    Regards
    Peter

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Assistance (97;SR2)

    Can do this with match and address etc but very messy. Instead create your own function:

    1. press Alt and F11
    2. menu: insert/module
    3. paste the following:

    Function CellAddress(lookupValue, LookupRange As Range)
    Dim counter As Integer
    Dim MyCell

    counter = 0
    For Each MyCell In LookupRange.Cells
    If lookupValue.Value = MyCell.Value Then
    counter = counter + 1
    If counter = 1 Then CellAddress = MyCell.Address 'picks first occurance
    Else
    End If
    Next MyCell
    End Function

    4. use the formula "=celladdress(b1,a1:e1)" where b1 is the entry to find (watch out for differences between text that looks like a date and a date (ie serial number)) and a1:e1 is the range to search. The formula will return the first occurance of the match

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Assistance (97;SR2)

    Peter, you responded with an easy solution while I was working on mine.

    I gues the main difference is that mine will work in any location while yours requires data to be in row specified in address formula.

    Please don't see my first post as an attack on yours

    thanks

    Simon

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Assistance (97;SR2)

    If you name the range "DataTable", and the desired date as "target", this will get the address, no matter where you move the range, no assumptions on row or column start, it figures it out.

    =ADDRESS(ROW(DataTable),MATCH(Target,DataTable,0)+ COLUMN(DataTable)-1)

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Assistance (97;SR2)

    Hi Simon

    'Attack'? Good heavens, hadn't realised we were that sensitive in here! (Humour, ok!). Actually, Steve posted an even better solution than mine. I'm just not that comfortable with macro/vba solutions and so tend to avoid them. Not to say that I don't use VBA, just limit it. As a consequence I have some amazingly clever single cell forumlas (formulae?) that are both indecipherable and probably more difficult to maintain than they ought to be.

    Besides, one of the great things about this place is the variety of solutions offered to a single challenge. Somedays I make greater use of that sledge hammer than others. ;-)

    Regards
    Peter

Posting Permissions

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