Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to Index Match Zip Code column 1

Status
Not open for further replies.

sue0912

MIS
Jun 19, 2007
50
US
Hi,

i can not quite figure out what is wrong with my index/match formula.

I have 1 spreadsheet and trying to index/match from 2 worksheets within the spreadsheet.

Sheet1 has 2 columns
Counties
Zipcodes

Sheet2 has my employee information with zipcodes

id
fname
lname
city
state
ny
county
zipcode

I am trying to populate my County column on Sheet2 with the following formula:
=INDEX(Sheet1!B:B,MATCH(G2,Sheet1!$A$1:$A$2036,0))

My results are coming back with #NA.

Can someone point me in the right direction of what i am doing incorrectly?

Thanks
Sue

 
Hi Sue,

For starters, your INDEX and MATCH functions span different row ranges. Try:
=INDEX(Sheet1!B$1:B$2036,MATCH(G2,Sheet1!$A$1:$A$2036,0))
or
=INDEX(Sheet1!B:B,MATCH(G2,Sheet1!$A:$A,0))




Cheers
[MS MVP - Word]
 
Can you show the data that is returning the #N/A ... i.e. the contents of G2, and also the cell contents that are supposed to match, and it's location? ( I assume that you've checked to make sure that neither cell has trailing spaces ).

When developing formulae like this, to trace errors I try things like using a formula similar to =G2=A735 to find out whether Excel is calculating the cells to be the same or not.

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
ok. my sheet2 has the following kind of data


ID FName LName City State County Zip
74980 FName LName AUBURN NY #N/A 13021
77363 FName LName AUBURN NY #N/A 13021
77506 FName LName AUBURN NY #N/A 13021
75084 FName LName AUBURN NY #N/A 13021
63692 FName LName AUBURN NY #N/A 13021
77418 FName LName AUBURN NY #N/A 13021
55259 FName LName AUBURN NY #N/A 13021
79372 FName LName Auburn NY #N/A 13021
75040 FName LName AUBURN NY #N/A 13021


Sheet1 has the following data

Zip County
13071 Cayuga
13139 Cayuga
13147 Cayuga
13140 Cayuga
13064 Cayuga
13024 Cayuga
13022 Cayuga
13021 Cayuga
13034 Cayuga
13033 Cayuga
13026 Cayuga
13081 Cayuga
13113 Cayuga
13118 Cayuga
13111 Cayuga
13117 Cayuga
13160 Cayuga
13156 Cayuga
13166 Cayuga
13092 Cayuga

thanks for you help
Sue
 


Hi,

I believe that you have 'Numbers' that are not Numbers.

faq68-6659.

Zip codes certainly APPEAR to be Numbers. They have 5 numeric characters. What you probably have is your County Lookup Table Zip column is formatted TEXT and your lookup ID column is formatted General.

However, merely FORMATTING the ID column as TEXT will not solve your problem. You might need to do this...
[tt]
=INDEX(Sheet1!B$1:B$2036,MATCH(Text(G2,"00000"),Sheet1!$A$1:$A$2036,0))
[/tt]

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
You are correct. I started to look at the format of the cells and noticed when clicked in and out of the cell, the county cell with the index/match forumal worked.

Sorry! My bad.

Sue
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top