Return cell address of matching cell within a range

[Keyser_Soeze]

I'd like a formula to return the address of a cell that has a specific value.

The range is A2:AD15 and the value only occurs in one cell in the range.

Something like ADDRESS(MATCH("value",A2:AD15)).

I've been looking through threads about INDEX and MATCH but I can't figure out on how to look for matches in a whole range and not just a single rows or columns.

Thanks!

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E
1	County	District	Ward
2	Cumbria	Eden	Ullswater
3	Shropshire	Shropshire	Cleobury Mortimer		Apples
4	West Yorkshire	Calderdale	Todmorden		$C$9
5	County Durham	County Durham	Weardale
6	Northumberland	Northumberland	South Tynedale
7	South Yorkshire	Sheffield	Stocksbridge and Upper Don
8	West Yorkshire	Bradford	Royds
9	West Yorkshire	apples	Craven
10	Lancashire	Rossendale	Irwell
11	West Yorkshire	Kirklees	Holme Valley North
12	Devon	Teignbridge	Moorland
13	County Durham	County Durham	Consett North
14	North Yorkshire	Richmondshire	Leyburn
15	North Yorkshire	Richmondshire	Hawes and High Abbotside
16
Master

Cell Formulas
Range	Formula
E4	E4	=ADDRESS(SUMPRODUCT((A2:C15=E3)*ROW(A2:A15)),SUMPRODUCT((A2:C15=E3)*(COLUMNS(A2:C2))))

 

[Keyser_Soeze]

Thanks! I think were getting there, the row gets right but the column is always the last one in the range as it also is in your example, "apples" is $B$9 and not $C$9.

 

[Fluff]

Slight typo, remove the S from the end of Columns.

 

[Keyser_Soeze]

That did it. Thanks a lot!

 

[Fluff]

You're welcome & thanks for the feedback.