How to retrieve Named Range name?

[Guntars]

Hello,
Is there a way to retrieve range name if value is in the range?
For example I got this column wit names:
name 1
name 2
name 3
name 4
name 5
name 6
name 7
name 8
name 9
name 10
name 11
name 12
This column has name ranges:
1st range called “CA†has these names:
name 1
name 2
name 3
name 4
2nd range called “DC†has these names:
name 5
name 6
name 7
name 8
name 9
And the 3rd range is called “WAâ€:
name 10
name 11
name 12
Lets say in different worksheet my formula returned “name1, name8 and name
11â€, in the same worksheet I would like to return the location or range name
associated with name.
Desired result:
__A_______B__
name 1___CA__
name 8___DC__
name 11__WA__
I think with nested IF functions I could achieve desired result; problem is
I am exceeding the limit of allowed nested functions. And I also don’t want
to add additional column in original data sheet were I specify the
location/range name for each name.
Any suggestions?
Thank you,
Guntars

 

[Charabeuh]

Hello,

something like that ?:

with a function in a module,

Function NameIS(xR) As String
NameIS = "No Name"
For Each Xname In ActiveWorkbook.Names
On Error GoTo Nextname
Set RR = Range(Mid(Xname.RefersTo, 2, 99))
For Each Xcell In RR
If Xcell.Value = xR.Value Then
NameIS = Xname.Name
Exit For
End If
Next Xcell
Nextname:
If NameIS <> "No Name" Then Exit For
Next Xname
End Function



Example

If cell C1 contains the string 'name 9'
if the formula in Cell D1 is '=NameIS(C1)'
The result in D1 shoud be 'DC'

 

[Shane Devenshire]

Hi,

I prefer Bernard's approach but here is a method you could use which matches
the question as you asked it:

=IF(OR(CA=E1),"CA",IF(OR(DC=E1),"DC",IF(OR(WA=E1),"WA")))

This formula assumes that the text you want to search for is in cell E1.
This is an array entered formula - Press Shift+Ctrl+Enter to enter it.