return a cell address based on a matched value..
 

This seems like it should be relatively straightforward but I can't
figure out a solution. Cell AO5 contains 12/1/2007. E5:P5 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

return a cell address based on a matched value..
 

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

Vaya con Dios,
Chuck, CABGx3




"Dave F" wrote:

This seems like it should be relatively straightforward but I can't
figure out a solution. Cell AO5 contains 12/1/2007. E5:P5 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

return a cell address based on a matched value..
 

Yeah, I need the cell's address.

On Jan 3, 1:17*pm, CLR wrote:
=HLOOKUP(AO5,E5:P8,4,FALSE) will return the VALUE in that cell........do you
actually need that cell's address/

Vaya con Dios,
Chuck, CABGx3



"Dave F" wrote:
This seems like it should be relatively straightforward but I can't
figure out a solution. *Cell AO5 contains 12/1/2007. *E5:P5 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- Hide quoted text -

- Show quoted text -

return a cell address based on a matched value..
 

Try this:

=ADDRESS(8,MATCH(AO5,E5:P5,0)+4,4)

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

=ADDRESS(ROWS(E1:E5)+3,MATCH(AO5,E5:P5,0)+COLUMNS( A5:D5),4)

--
Biff
Microsoft Excel MVP


"Dave F" wrote in message
...
This seems like it should be relatively straightforward but I can't
figure out a solution. Cell AO5 contains 12/1/2007. E5:P5 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

return a cell address based on a matched value..
 

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?

On Jan 3, 1:17*pm, CLR wrote:
=HLOOKUP(AO5,E5:P8,4,FALSE) will return the VALUE in that cell........do you
actually need that cell's address/

Vaya con Dios,
Chuck, CABGx3



"Dave F" wrote:
This seems like it should be relatively straightforward but I can't
figure out a solution. *Cell AO5 contains 12/1/2007. *E5:P5 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- Hide quoted text -

- Show quoted text -