Trying to Index Match Zip Code column 1

[sue0912]

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

 

[macropod]

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]

 

[GlennUK]

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.

 

[sue0912]

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

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sue0912]

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