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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Guntars" wrote:

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 dont want
to add additional column in original data sheet were I specify the
location/range name for each name.
Any suggestions?
Thank you,
Guntars