Compare values in two columns and return text from adjacent cell

MrBorders · #1 07-04-2009, 02:13 AM

Hoping somebody can assist me in solving the below problem - my XLSX file can be downloaded at: http://www.faithfold.com/download/zipcode_database.xlsx

I'm dealing with 4 columns of data (there are more in the file but only the first four are pertinent to the function). The columns, left to right, are: my zips, my cities, us zips, us cities.

Here is the scenario: I have a list (Column A) of zip codes that I need to match up with their corresponding cities. Column C is a much longer list of every zip code in the country, along with the corresponding city name adjacent to it, in Column D.

What I need Excel to do is look at the Zips in Column A, find the match in Column C, and then take the adjacent city name in Column D, and place that city name back in Column B... I hope that makes sense. In short, I just need to gather the correct city names that match my zips, and the data is there I just don't know how to manipulate it.

Thank you!

bigmack · #2 07-04-2009, 02:33 AM

MrBorders,

You don't really need to use excel programming for this. There is a function(s) that can simply accomplish this task. For example, column A is your LOOKUP zipcodes, column C is a larger subset of zipcodes, column D is your corresponding city names, and column B is where you would like the city names to appear for each LOOKUP zipcodes.

In column B:

Code:

=INDEX(C1:D2,MATCH(A1,C1:C2,0),2)

MrBorders · #3 07-04-2009, 02:57 AM

Jimmie D,

I appreciate the response, thank you. I pasted this function into Column B and I'm getting #N/A. Any ideas? Would you mind downloading my XLS from the above link and seeing how it goes on your end? Again, thank you!

Tyler

MrBorders · #4 07-04-2009, 03:07 AM

Jimmie,

Got it to go, here's the mod: =INDEX(C:D,MATCH(A10,$C$1:$C$50000,0),2)

Thanks for your help. You loosened the lid, and I stole the credit for getting it off

Thanks brother!

Tyler

bigmack · #5 07-04-2009, 03:15 AM

MrBorders,

We're both using 2003 - Do you have a 2003 version saved by chance?

In the meantime, try the above formula create a sample worksheet to test it out.

Column A
11368
14228

Column B
Corona // should be returned from column D based on column A matching column C
Amherst // should be returned from column D based on column A matching column C

Column C
14228
11368

Column D
Amherst
Corona

bigmack · #6 07-04-2009, 03:17 AM

Awesome! Have a Happy 4th and mark this post as solved

dextras · #7 07-04-2009, 04:54 AM

^ You can mark it as solved by editing our first post, click go advanced and then click the drop down where it says "(no prefix)"

Welcome to the Excel Forum

Forum Statistics