Looking up Values for ranges of ZipCodes

jleasure457

New Member
Joined
Dec 27, 2018
Messages
9
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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’)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
Solution
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.
 
Upvote 0
Yup that's one of the nice things about xlookup & also xmatch, which has the same.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top