Return Cell Adress

[Caribeiro77]

Hy, i have this scenario:
From A1 to A1000 i have random data, and want i to write someting, for example in cell C1, and what i need is a formula to tell me if what i wrote in C1 is or isn't in that thousand cells range, and if it is, what's the cell adress?

Thanks..

 

[VoG]

Try

=IF(ISNUMBER(MATCH(C1,A:A,0)),"A"&MATCH(C1,A:A,0),"")

 

[Caribeiro77]

I understood your formula, you concatenate de "A", with the ordinal position of the data, but what if i want the extend the range of data, let's say to A1:D500, I think i no longer can't use this formula because the concatenation won't work with this range.
New solution?

Thanks once again...

Try

=IF(ISNUMBER(MATCH(C1,A:A,0)),"A"&MATCH(C1,A:A,0),"")

 

[VoG]

Try this UDF in a standard module

Function RetAdd(r As Range, c As Range) As String
Dim Found As Range
Set Found = r.Find(what:=c.Value, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    RetAdd = "Not found"
Else
    RetAdd = Found.Address(False, False)
End If
End Function

To use, for example

=retadd(A1:D20,F1)

 

[Caribeiro77]

working.
Can you tell me if this code can be used every time i open excel, or only works in this workbook??

Try this UDF in a standard module

Function RetAdd(r As Range, c As Range) As String
Dim Found As Range
Set Found = r.Find(what:=c.Value, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    RetAdd = "Not found"
Else
    RetAdd = Found.Address(False, False)
End If
End Function

To use, for example

=retadd(A1:D20,F1)

 

[VoG]

It only works in that workbook. You could place the code in personal.xls http://www.rondebruin.nl/personal.htm

Then you would need

=personal.xls!retadd(A1:D20,F1)

 

[Caribeiro77]

Thanks...

It only works in that workbook. You could place the code in personal.xls http://www.rondebruin.nl/personal.htm

Then you would need

=personal.xls!retadd(A1:D20,F1)