Looking up Values for ranges of ZipCodes

jleasure457

New Member
Joined
Dec 27, 2018
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am in need of a formula that can lookup up a zip code from a cell and reference the correct rate based off of the table below. The way the table below works is that any zip code equal to or less than the given zip code correlates to that rate. So for example, anything zip code less than or equal to 90213=1.5. Zip codes greater than or equal to 90214 or less than or equal to 90219 = 1. The formula would need to look up a zip code on another table and, reference the below table, and return the correct rate based on where the given zip code falls within the range described.

ZIP_CODERATE
90213​
1.5​
90219​
1​
90224​
1.5​
90229​
1​
90233​
1.5​
90238​
1​
90242​
1.5​
90244​
1​
90245​
1.5​
90246​
1​
90251​
1.5​
90253​
1​
90255​
1.5​
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,551
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

jleasure457

New Member
Joined
Dec 27, 2018
Messages
6
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Thank you! I'm using O365 and have updated my account details.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,551
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDE
1ZIP_CODERATE
2902131.5902101.5
3902191902331.5
4902241.5902341
5902291
6902331.5
7902381
8902421.5
9902441
10902451.5
11902461
12902511.5
13902531
14902551.5
15
Master
Cell Formulas
RangeFormula
E2:E4E2=XLOOKUP(D2,$A$2:$A$14,$B$2:$B$14,"",1)
 
Solution

jleasure457

New Member
Joined
Dec 27, 2018
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDE
1ZIP_CODERATE
2902131.5902101.5
3902191902331.5
4902241.5902341
5902291
6902331.5
7902381
8902421.5
9902441
10902451.5
11902461
12902511.5
13902531
14902551.5
15
Master
Cell Formulas
RangeFormula
E2:E4E2=XLOOKUP(D2,$A$2:$A$14,$B$2:$B$14,"",1)
Thank you! I didn't realize XLOOKUP had the "next smaller item"/"next larger item" function. This is really helpful.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,551
Office Version
  1. 365
Platform
  1. Windows
Yup that's one of the nice things about xlookup & also xmatch, which has the same.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,539
Messages
5,659,392
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top