1. ## 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. ## Re: Formula Assistance (97;SR2)

Something like:

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. ## Re: Formula Assistance (97;SR2)

1. press Alt and F11
3. paste the following:

Dim counter As Integer
Dim MyCell

counter = 0
For Each MyCell In LookupRange.Cells
If lookupValue.Value = MyCell.Value Then
counter = counter + 1
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. ## 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. ## 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.

Steve

6. ## 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
•