Zip Code Lookup

[iellingson]

I have looked all over and can't find a solution for this.

I have two columns in one sheet that contain origin & destination zip codes.
I have two columns in a second sheet that contain a low value zip code and a high value zip code.

What I am looking to do is find what range this zip code falls and place a value or that range in the cell next to it on the first sheet.

Example

Sheet 1
A       B                          C       D
90210   (CA1 or 90001-92001)       60601   (IL1 or 60001-62001)

Sheet 2
A       B       C     D       E      F
90001   92001   CA1   60001   62001  IL1

I have 41,000 rows in Sheet 1 and 254 rows in Sheet 2.

 

[GNB]

Example

Sheet 1
A       B                          C       D
90210   [RANGE=cls:xl2bb-100][XR][XH=cs:4]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][/XR][XR][XH]1[/XH][XD=h:r]90210[/XD][XD=h:l]CA1[/XD][XD=h:l](CA1 90001 - 92001)[/XD][/XR][XR][XH]2[/XH][XD=h:r]60601[/XD][XD=h:l]IL1[/XD][XD][/XD][/XR][XR][XH=cs:4][RANGE][XR][XD](CA1 or 90001-92001)       60601   (IL1 or 60001-62001)

Sheet 2
A       B       C     D       E      F
90001   92001   CA1   60001   62001  IL1

I have 41,000 rows in Sheet 1 and 254 rows in Sheet 2.

So are you looking to get a formula to calculate the bold sections?

B gives you just CA. C gives you what you have above. C requires you to concatenate A&B as an example on sheet 2 in col D.
Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]Excel 2007

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,Sheet2!$A$1:$C$3,3,TRUE)
B2	=VLOOKUP(A2,Sheet2!$A$1:$C$3,3,TRUE)
C1	=CONCATENATE("(",B1," ",VLOOKUP(A1,Sheet2!$A$1:$D$3,4,TRUE),")")

HTH

 

[iellingson]

So are you looking to get a formula to calculate the

Excel Workbook
	A	B	C
1	90210	CA1	(CA1 90001 - 92001)
2	60601	IL1
bold sections? B gives you just CA. C gives you what you have above. C requires you to concatenate A&B as an example on sheet 2 in col D. Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,Sheet2!$A$1:$C$3,3,TRUE)
B2	=VLOOKUP(A2,Sheet2!$A$1:$C$3,3,TRUE)
C1	=CONCATENATE("(",B1," ",VLOOKUP(A1,Sheet2!$A$1:$D$3,4,TRUE),")")

HTH

What I am looking for is for zip codes to return a value based upon the range they fall in. Example 90210 falls within CA1 but since that is a range I can't use VLookup.

 

[GNB]

What I am looking for is for zip codes to return a value based upon the range they fall in. Example 90210 falls within CA1 but since that is a range I can't use VLookup.

If you use true instead of false as your last argument you can look up a range. Just need to separate the range into two columns first and then look up the start of the range.

In the example I put down if you put 90211 in the first column you should return CA1 as well.

 

[iellingson]

If you use true instead of false as your last argument you can look up a range. Just need to separate the range into two columns first and then look up the start of the range.

In the example I put down if you put 90211 in the first column you should return CA1 as well.

Awesome. Thanks.