MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with IF statement...Please?


Posted by Fred on January 10, 2001 2:29 PM

I need to make a zipcode lookup spreadsheet.

I have 5 zones in a state that all have groups of zipcodes in them. I want to be able to enter a zipcode and have it tell me what zone it is from and who it belongs to.

Problem is, with the IF statement, I can't find a way to do it without lots of repeating. Here is what I tried:

=IF(B3="07711, 07723, 07712, 07755, 07764, 07724, 07757, 07740, 07703, 07750, 07702, 07739, 07704, 07701, 07738, 07722, 07760, 07716, 07737, 07718, 07728, 07746, 07751, 07747, 07735, 07721, 08879, 08859, 08857, 08884, 07716, 07726, 08831, 08828, 08872","",True")

Yeah it would be nice if it worked but I KNOW it does not make sense. Anyone? Please?

Fred


Posted by Mark W. on January 10, 2001 2:43 PM

Fred, setup 3 columns: ZIPCODE, ZONE, PERSON

I recommend that you format the ZIPCODE column as
Text before you enter any data.

Use:

=VLOOKUP(zip,lookup_range,2,0) to lookup a ZONE &
=VLOOKUP(zip,lookup_range,3,0) to lookup a PERSON

where 'zip in the code you want to lookup (e.g.,
"07711" and 'lookup_range' are the cells where you
entered all our data (e.g., A2:C500)

Posted by Fred on January 10, 2001 2:59 PM

Hrmmm...never used the vlookup function. I think I understand but, I will need to have a cell where I enter the zipcode itself. Would there still be a need to use the IF statement?

Fred


Posted by Mark W. on January 10, 2001 3:04 PM

If you going to enter the zipcode to be looked up
into a cell then put the cell reference in the
VLOOKUP() function as the 1st argument, "zip".

Nope, no need for an IF().

Posted by Fred on January 10, 2001 3:17 PM

Ok let me start from the beginning.

I have 5 salespeople and 5 zones in NJ. Each of the salespeople has a zone that contains a certain number of zipcodes. I have broken down the zipcodes for each zone already.

What I want to do is be able to enter a zipcode and have it spit out what zone and what salesperson the zone belongs to.

Are we on the right track with this or have I confused the issue? :)

Fred

Posted by Mark w. on January 10, 2001 3:29 PM

...should work!

Posted by Fred on January 10, 2001 3:34 PM

Sot of works. I get a #REF when I have a match to a zip in the list but I get a NA when I don't match it.

3 Columns:

Zipcode ! Zone ! Salesperson

Posted by Mark W. on January 11, 2001 4:23 AM

Can I see your formula?

Posted by Fred on January 11, 2001 6:23 AM

Would it be ok if I emailed you the excell file? This way you can see how nuts I am. :)

Fred