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
