find county using zipcode

[dirk_pepperd]

Good Morning! I've spend at least 8 hours on this and can't seem to get it right. I'm just trying to cross reference a county from a zip code in another table. I'm trying to use something simple like and IF statement but I've also tried VLOOKUP as index/match.

I'm just trying to get the correct County to populate on the main sheet in Column N from the Florida Zip Codes sheet. No matter how i format the cell i keep coming up blank. Thanks in advance to anyone that can help me figure it out.

Dirk

Here's the spreadsheet link: ZipCodeLookup.xlsx

 

[Marcílio_Lobão]

dirk_pepperd, Good morning.

Try to use: N2 --> =IFERROR( VLOOKUP(E2;newzips;5;FALSE); "-- // --")

Copy it down till N26.

Attention: Only 4 records are from Florida.

Please, tell us if it worked as you want.

I hope it helps.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=XLOOKUP(E2,Table1[zip],Table1[county_name],"NA",0)

 

[dirk_pepperd]

Eureka!!! I tried the XLOOKUP one and it works!!!! I'm by no means and Excel genius (but a friend of mine really is and it even stumped him). So this begs the question.... Why did we have so much problem using the IF statement (and even VLOOKUP) trying to get this done and XLOOKUP seems to work flawlessly ? Thanks so much for helping resolve this I seriosly just spend hours and hours on it.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[dirk_pepperd]

Glad we could help & thanks for the feedback.

Just a few questions so I understand the "Why" behind the solution please?

• Why did XLOOKUP work and VLOOKUP seemed to have an issue with the formula calculation?
• The reference to "Table 1" instead of the name of the other tab? (The reason I ask is because I added another tab that I want to reference different data from).

Thanks again for your help!!!

Dirk

 

[Fluff]

I have no idea why the vlookup you tried didn't work as it should work.
As you had the data in a structured table I used that form of referencing rather than sheet name & ranges, because it's safer to some extent.