Code or formula for State lookup

[ccampb18]

I am looking for a code or formula that will give the State of the range of zip codes that are entered but the zips only include the first 3 digits. For example: If I have a range of zips, 480-599, I would want excel to spit out all the States in b/w that range. I already have a table with the States and zips and I have a formula if you type in a 3 digit zip it will spit out the state but I am needing a range of zips to spit out all the states.

 

[Gary Drumm]

Are you saying that a particular zip might be applicable to more than one state, and that you need to be able to render those states in as search?

Can you post some samples of what you have, and a sample of your desired result?

 

[ccampb18]

This may be something suited best for Access but it seems like there would be a way to do it in excel just because I am an excel fan. But lets say I type in a box 480 for the first parameter value and I type 599 in a second box for a second parameter value. I would then want excel to list all the States in between those zips. So for this example I would want to see MI, IA, WI, MN, SD, ND, MT. I tried to set up a Vlookup but I think it is a little complex for it so I was thinking I might need a macro to perform. Does that clarify at all?

 

[Gary Drumm]

You would need the master list which details all of the 33,000 + zip codes, segregated by the corresponding states, DC, and Puerto Rico.

That would need to be loaded into Excel, probably in descending numeric order for the far left column of sheet 2, with the states arranged alongside, in column B of the same worksheet.

Then a lookup or match formula could be written to produce the results you require.