return a cell address based on a matched value..

[Dave F]

This seems like it should be relatively straightforward but I can't
figure out a solution. Cell AO5 contains 12/1/2007. E55 contains:
1/1//2007
2/1/2007
3/1/2007
4/1/2007
5/1/2007
6/1/2007
7/1/2007
8/1/2007
9/1/2007
10/1/2007
11/1/2007
12/1/2007.

I want to return the cell address three rows below row 5, which
corresponds to the value in AO5. In this case, I would want cell
address P8 returned, since P5 contains the value 12/1/2007.

Any ideas as to how to approach this problem?

Thanks.

Dave

 

[CLR]

=HLOOKUP(AO5,E58,4,FALSE) will return the VALUE in that cell........do you
actually need that cell's address/

Vaya con Dios,
Chuck, CABGx3

 

[Dave F]

Yeah, I need the cell's address.

=HLOOKUP(AO5,E58,4,FALSE) will return the VALUE in that cell........doyou
actually need that cell's address/

Vaya con Dios,
Chuck, CABGx3







- Show quoted text -

 

[T. Valko]

Try this:

=ADDRESS(8,MATCH(AO5,E55,0)+4,4)

Or, to make it robust against row/column insertions:

=ADDRESS(ROWS(E1:E5)+3,MATCH(AO5,E55,0)+COLUMNS(A55),4)

 

[Dave F]

Well, this formula appears to return the cell address:
=ADDRESS(8,COLUMN(OFFSET(E5,3,MATCH(AO5,E5:R5,0)-1,,)))

Any way of simplifying that?