I want to see if a value in A1 is present in an array, say B1:D15, and then
return the refernce of the cell in which the value is held. The value in A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.

Try this small UDF:

Function WhereInTheWorld(rf As Range, r As Range) As String
Dim rr As Range
WhereInTheWorld = "No Luck"
v = rf.Value
For Each rr In r
If rr.Value = v Then
WhereInTheWorld = Replace(rr.Address, "$", "")
Exit Function
End If
Next
End Function

and use in the worksheet like:

=WhereInTheWorld(A1,B1:D15)

--
Gary''s Student - gsnu200846


"Hugh" wrote:

I want to see if a value in A1 is present in an array, say B1:D15, and then
return the refernce of the cell in which the value is held. The value in A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.

This array* formula will also work, returns format of "D4":

=ADDRESS(MAX(IF(B1:D15=A1,ROW(B1:D15))),MAX(IF(B1: D15=A1,COLUMN(B1:D15))),4)

*Confirm formula using Ctrl+shift+Enter

If you want an absolute reference returns ($D$4) remove the 4 from last
arguement of the address function.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hugh" wrote:

I want to see if a value in A1 is present in an array, say B1:D15, and then
return the refernce of the cell in which the value is held. The value in A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.

Using your example,
try this:

=IF(COUNTIF(B1:D15,A1),ADDRESS(MAX(INDEX((B1:D15=A 1)*
ROW(1:15),0)),MAX(INDEX((B1:D15=A1)*
COLUMN(B:D),0))),"no match")

or...shorter...but needs CTRL+SHIFT+ENTER:
=IF(COUNTIF(B1:D15,A1),ADDRESS(MAX((B1:D15=A1)*
ROW(1:15)),MAX((B1:D15=A1)*COLUMN(B:D))),"no match")

With that example, the formulaS return: $D$4

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"Hugh" wrote in message
...
I want to see if a value in A1 is present in an array, say B1:D15, and
then
return the refernce of the cell in which the value is held. The value in
A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.

Hi Gary and thanks for the reply. I have no prior experience of UDFs, but
Google has helped me out there.
I've tried the UDF and it works, brilliantly. But I have to save the
workbook as "macro enabled", which our network at work doesn't like. Can
this be avoided?
Thanks again.

"Gary''s Student" wrote:

Try this small UDF:

Function WhereInTheWorld(rf As Range, r As Range) As String
Dim rr As Range
WhereInTheWorld = "No Luck"
v = rf.Value
For Each rr In r
If rr.Value = v Then
WhereInTheWorld = Replace(rr.Address, "$", "")
Exit Function
End If
Next
End Function

and use in the worksheet like:

=WhereInTheWorld(A1,B1:D15)

--
Gary''s Student - gsnu200846


"Hugh" wrote:

I want to see if a value in A1 is present in an array, say B1:D15, and then
return the refernce of the cell in which the value is held. The value in A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.

There are parms you can specify in the .address property that will show the
address as relative references.

rr.address(0,0)
or
rr.address(false,false)

without using keywords.

Gary''s Student wrote:

Try this small UDF:

Function WhereInTheWorld(rf As Range, r As Range) As String
Dim rr As Range
WhereInTheWorld = "No Luck"
v = rf.Value
For Each rr In r
If rr.Value = v Then
WhereInTheWorld = Replace(rr.Address, "$", "")
Exit Function
End If
Next
End Function

and use in the worksheet like:

=WhereInTheWorld(A1,B1:D15)

--
Gary''s Student - gsnu200846

"Hugh" wrote:

I want to see if a value in A1 is present in an array, say B1:D15, and then
return the refernce of the cell in which the value is held. The value in A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.



--

Dave Peterson

That's really useful, Luke. Thanks.

"Luke M" wrote:

This array* formula will also work, returns format of "D4":

=ADDRESS(MAX(IF(B1:D15=A1,ROW(B1:D15))),MAX(IF(B1: D15=A1,COLUMN(B1:D15))),4)

*Confirm formula using Ctrl+shift+Enter

If you want an absolute reference returns ($D$4) remove the 4 from last
arguement of the address function.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hugh" wrote:

I want to see if a value in A1 is present in an array, say B1:D15, and then
return the refernce of the cell in which the value is held. The value in A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.

Thanks for your efforts, Ron, but I can't get this to work. I think the
trouble is that there is other data in rows 1 to 15 besides that which is in
the range B1:D15. I could transplant the data, I guess...

"Ron Coderre" wrote:

Using your example,
try this:

=IF(COUNTIF(B1:D15,A1),ADDRESS(MAX(INDEX((B1:D15=A 1)*
ROW(1:15),0)),MAX(INDEX((B1:D15=A1)*
COLUMN(B:D),0))),"no match")

or...shorter...but needs CTRL+SHIFT+ENTER:
=IF(COUNTIF(B1:D15,A1),ADDRESS(MAX((B1:D15=A1)*
ROW(1:15)),MAX((B1:D15=A1)*COLUMN(B:D))),"no match")

With that example, the formulaS return: $D$4

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"Hugh" wrote in message
...
I want to see if a value in A1 is present in an array, say B1:D15, and
then
return the refernce of the cell in which the value is held. The value in
A1
will not occur more than once in the array.

For instance, if the value in A1=30, and 30 appears in cell D4, I want to
return D4 (or $D$4).

Is there a formula or function that will return the cell reference of cell
containing a specific value?

Thanks in advance.