# Looking up Values for ranges of ZipCodes

#### jleasure457

##### New Member
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_CODE RATE 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
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
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
+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)

#### jleasure457

##### New Member
+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
Yup that's one of the nice things about xlookup & also xmatch, which has the same.

Replies
3
Views
197
Replies
3
Views
157
Replies
9
Views
433
Replies
4
Views
231
Replies
7
Views
598

1,133,539
Messages
5,659,392
Members
418,500
Latest member

### 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.

### Which adblocker are you using?

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

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