Why use range names?
On the first sheet use
name 1 CA
name 2 CA
name 3 CA
name 4 CA
name 5 DC
etc
with the names in say column A and the codes in column B
On the other sheet
A1 B1
name 1 =VLOOKUP(A1,Sheet1!A1:B1000,2,FALSE)
if name 1 is found in column A the formula will return the code from the B
column
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"Guntars" wrote in message
...
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