Excel 2007 - i made it disappear!

[Sutter'sBeard]

Statman,

Since you appear to be an excel expert, I am trying to find a formula where a cell will automatically display a postage zone when I type the zip in another cell. In other words, if I type in a zip code in column a2, column b2 will display the correct postal zone from a chart/table that I have created. Can you give me any guidance on this please?

Sure thats an easy one. You'd do it like this:

suppose on Sheet2 you have a table

Code: Select all

       A                B
1  ZIP        postage zone
2  60103     Bartlett, IL
3  60104     Skokie, IL
4  ...          ....
...
218   99999  Gooba, Guam

etc.

make sure the list is sorted in order by Zip Code.

now on Sheet1 let's say you have:

Code: Select all

         A          B           C
1
2   Zip ?        ______
3   Zone        ______
4

you want to be able to type a zip code in cell B2 and have the postage zone come up in cell B3...

in B3 you would use the VLOOKUP( ) function... (vertical lookup)

=VLOOKUP( B2, Sheet2!A2:B218, 2 )

if you want to copy the formula to do the same thing for 2 different cells (to compare zip codes, maybe) you might want to use absolute reference like this

=VLOOKUP (B2, Sheet2!$A$2:$B$218, 2)

you can get fancier, but VLOOKUP is the easiest way to go.


if the list is incomplete or if you want to make sure you have a match, there is another flag you can add. the default i believe is that it finds the first match or if no match, first value greater than the match.

so if there was no 60103 in your list it might show the zone for the next entry, 60104 or whatever was next highest.

questions? clear as mud?

Wholly crap, thank you very much. This will make the most hated part of my work day much easier. Thanks again.

[StatmanCrothers]

well i somehow managed to make it sound complicated, but once you get the hang of it, its pretty painless.