I have two tabs I have one tab that has information Like mailing address city
state and some more. I have another tab that has citys with a id code. I
want to do a function that if City on tab 1 matches city on tab 2 then that
code.
So if I have Address Nikiski AK 99999
THen the Code for Nikiski on tab 2 would appear.
Thanks
Cheyenne

try a vlookup function.

let's say tab (worksheet) 2 that has the City ID code is named "ID". Then
let's say you have 16 Cities on your list which is entered on A1:A16 and then
the corresponding code is on B1:B16.

On your first tab (worksheet) where you have the complete mailing address,
let's say your city is on cell G1 and you want the ID code to appear on H1
based on G1's entry. Type this formula on H1:

=if(isblank(G1),"",vlookup(G1,ID!$A$1:$B$16,2,FALS E))

that should work!

"Chey" wrote:

I have two tabs I have one tab that has information Like mailing address city
state and some more. I have another tab that has citys with a id code. I
want to do a function that if City on tab 1 matches city on tab 2 then that
code.
So if I have Address Nikiski AK 99999
THen the Code for Nikiski on tab 2 would appear.
Thanks
Cheyenne

=IF(ISBLANK(D3),"",VLOOKUP(D3,[ID]Sheet1!$A$2:$B$600,2,FALSE))
This is what I end up with but then it says NA

D3 is the City on My primary sheet and Sheet1!$A$2:$B$600 is the range of my
codes on my other sheet.
Is this right?

"Storm" wrote:

try a vlookup function.

let's say tab (worksheet) 2 that has the City ID code is named "ID". Then
let's say you have 16 Cities on your list which is entered on A1:A16 and then
the corresponding code is on B1:B16.

On your first tab (worksheet) where you have the complete mailing address,
let's say your city is on cell G1 and you want the ID code to appear on H1
based on G1's entry. Type this formula on H1:

=if(isblank(G1),"",vlookup(G1,ID!$A$1:$B$16,2,FALS E))

that should work!

"Chey" wrote:

I have two tabs I have one tab that has information Like mailing address city
state and some more. I have another tab that has citys with a id code. I
want to do a function that if City on tab 1 matches city on tab 2 then that
code.
So if I have Address Nikiski AK 99999
THen the Code for Nikiski on tab 2 would appear.
Thanks
Cheyenne

first: on your formula take out [ID]. My example is assuming you already
named the sheet but it appears from your formula that it hasn't (signified by
"Sheet1")

second: make sure that on the code list, the city name is on the left column
and the ID code is on the right ~~ hence the formula is saying "A1:B600,2,
false" where it will lookup the exact value of D3 from A1 and return the
corresponding value from the 2nd column.

third: the #N/A error means that the value on D3 cannot be located in the
sheet where you have the ID codes. To be able to use the vlookup formula,
the value should be exactly the same. So let's say D3 shows SanDiego but
your code list shows San Diego, it won't be able to find it since it is not
exactly the same because of the missing space between San and Diego. So you
have to make sure the value you have on D3 is the same as how it is mentioned
on your ID codes. If you still come up with the same error, it just means
that the value on D3 is not included in your list on the ID codes.

You can append the formula to state that if it comes up as an error to just
not put anything but i guess that really wouldn't help you since without that
"#NA" then you wouldn't even know that your report needs your attention.

"Chey" wrote:

=IF(ISBLANK(D3),"",VLOOKUP(D3,[ID]Sheet1!$A$2:$B$600,2,FALSE))
This is what I end up with but then it says NA

D3 is the City on My primary sheet and Sheet1!$A$2:$B$600 is the range of my
codes on my other sheet.
Is this right?

"Storm" wrote:

try a vlookup function.

let's say tab (worksheet) 2 that has the City ID code is named "ID". Then
let's say you have 16 Cities on your list which is entered on A1:A16 and then
the corresponding code is on B1:B16.

On your first tab (worksheet) where you have the complete mailing address,
let's say your city is on cell G1 and you want the ID code to appear on H1
based on G1's entry. Type this formula on H1:

=if(isblank(G1),"",vlookup(G1,ID!$A$1:$B$16,2,FALS E))

that should work!

"Chey" wrote:

I have two tabs I have one tab that has information Like mailing address city
state and some more. I have another tab that has citys with a id code. I
want to do a function that if City on tab 1 matches city on tab 2 then that
code.
So if I have Address Nikiski AK 99999
THen the Code for Nikiski on tab 2 would appear.
Thanks
Cheyenne

Okay let me try again.
I renamed my Sheet ID
City ID
Unknown 0200000
Adak Island, AK 0200100
Afognak (ANV), AK 0200430
Aguikchuk, AK 0200540
Akhiok, AK 0200650
Akiachak, AK 0200760
Akiak, AK 0200870
Akulurak, AK 0201020
Akumsuk, AK 0201050
Akutan, AK 0201090
Alaganik, AK 0201150
Alakanuk, AK 0201200
Aleknagik, AK 0201420
Aleksashkina, AK 0201550
Alexanders Village, AK 0201670
Allakaket, AK 0201860
Alyeska, AK 0201900
Ambler, AK 0201970
Anaktuvuk Pass, AK 0202080
Anchorage, AK 0203000
Anchor Point, AK 0203110
Anderson, AK 0203220
Angoon, AK 0203440
Aniak, AK 0203550

Just some of the villages

Now heres the sheet I want them to go to
These are in Columns A and B down to 600

Akiachak, AK #N/A AK
Anchor Point, AK AK
Anchor Point, AK AK
Aniak, AK AK
Bethel, AK AK
Bethel, AK AK
Bethel, AK AK
Bethel, AK AK
Bethel, AK AK
Big Lake, AK AK
Big Lake, AK AK
Copper Center, AK AK
Cordova, AK AK
Cordova, AK AK
Craig, AK AK
Delta Junction, AK AK
Delta Junction, AK AK
Delta Junction, AK AK
Dillingham, AK AK
Douglas, AK AK
Douglas, AK AK
Douglas, AK AK
Douglas, AK AK

Between the village and AK is where the code goes.
You can see I have them in both tables

The Villages start in D3 I want the function in E3

So this is the function
=IF(ISBLANK(D3),"",VLOOKUP(A2,ID!$A$2:$B$600,2,FAL SE))
Thanks again



"Storm" wrote:

first: on your formula take out [ID]. My example is assuming you already
named the sheet but it appears from your formula that it hasn't (signified by
"Sheet1")

second: make sure that on the code list, the city name is on the left column
and the ID code is on the right ~~ hence the formula is saying "A1:B600,2,
false" where it will lookup the exact value of D3 from A1 and return the
corresponding value from the 2nd column.

third: the #N/A error means that the value on D3 cannot be located in the
sheet where you have the ID codes. To be able to use the vlookup formula,
the value should be exactly the same. So let's say D3 shows SanDiego but
your code list shows San Diego, it won't be able to find it since it is not
exactly the same because of the missing space between San and Diego. So you
have to make sure the value you have on D3 is the same as how it is mentioned
on your ID codes. If you still come up with the same error, it just means
that the value on D3 is not included in your list on the ID codes.

You can append the formula to state that if it comes up as an error to just
not put anything but i guess that really wouldn't help you since without that
"#NA" then you wouldn't even know that your report needs your attention.

"Chey" wrote:

=IF(ISBLANK(D3),"",VLOOKUP(D3,[ID]Sheet1!$A$2:$B$600,2,FALSE))
This is what I end up with but then it says NA

D3 is the City on My primary sheet and Sheet1!$A$2:$B$600 is the range of my
codes on my other sheet.
Is this right?

"Storm" wrote:

try a vlookup function.

let's say tab (worksheet) 2 that has the City ID code is named "ID". Then
let's say you have 16 Cities on your list which is entered on A1:A16 and then
the corresponding code is on B1:B16.

On your first tab (worksheet) where you have the complete mailing address,
let's say your city is on cell G1 and you want the ID code to appear on H1
based on G1's entry. Type this formula on H1:

=if(isblank(G1),"",vlookup(G1,ID!$A$1:$B$16,2,FALS E))

that should work!

"Chey" wrote:

I have two tabs I have one tab that has information Like mailing address city
state and some more. I have another tab that has citys with a id code. I
want to do a function that if City on tab 1 matches city on tab 2 then that
code.
So if I have Address Nikiski AK 99999
THen the Code for Nikiski on tab 2 would appear.
Thanks
Cheyenne