I have several vertical ranges of zip codes with each range equaling a specific delivery cost. If I type the a zip code in one cell, I want the cell next to it to show the price of delivery. I have been successful with different formulas, but they have only worked with one range at a time. I can't get my multiple ranges nested correctly in the cell I want to display the cost. So basically I'm going about it this way, If this cell equals a number in this range, then return this value, if not return nothing. What would be the best way to approach this?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Finding the correct formula for multiple ranges of zip codes to determine shipping cost.
Can you provide an example?
Seems like a pretty straight forward use of vlookup or index/match functions
Upvote
0
Sure...
=IF(COUNTIF(K3:K16,C32),100,"")
&
=IF(ISNA(MATCH(C32,K3:K16,0)),"",100)
C32 is where the initial zip code is entered, while 100 is the value I need to return for the delivery cost, but I have several ranges and price differences to add to this formula.
Both seem to work but I can't successfully add more ranges to the formula. I'm new to excel, so my knowledge it limited and my syntax is not fully realized.
=IF(COUNTIF(K3:K16,C32),100,"")
&
=IF(ISNA(MATCH(C32,K3:K16,0)),"",100)
C32 is where the initial zip code is entered, while 100 is the value I need to return for the delivery cost, but I have several ranges and price differences to add to this formula.
Both seem to work but I can't successfully add more ranges to the formula. I'm new to excel, so my knowledge it limited and my syntax is not fully realized.
Upvote
0
Try provide an example of the sheet you are working with and the associated formula.
Still a but tricky to work out what you want.
But here is what I think you want.
See example below
You have a list of zip codes and associated amounts (D1:E7)
You want to enter in a zip code in A2 to return the result in B2
If this is the case use =vlookup(A2,D2:E7,2,False) (be careful there are limits to using vlookup and index/match is a better function. But this will work)
<tbody>
</tbody>
Still a but tricky to work out what you want.
But here is what I think you want.
See example below
You have a list of zip codes and associated amounts (D1:E7)
You want to enter in a zip code in A2 to return the result in B2
If this is the case use =vlookup(A2,D2:E7,2,False) (be careful there are limits to using vlookup and index/match is a better function. But this will work)
| A | B | C | D | E | |
| 1 | Zip | Amount | Zip | Amount | |
| 2 | 4 | 404 | 1 | 100 | |
| 3 | 2 | 200 | |||
| 4 | 3 | 300 | |||
| 5 | 4 | 404 | |||
| 6 | 5 | 500 | |||
| 7 | 6 | 600 |
<tbody>
</tbody>
Upvote
0
Without getting too complicated, you can just continue what you are doing using more if functions.
Not the prettiest solution but it'll work. Then you can just repeat this for however many columns you want
=IF(COUNTIF(L3:L34,$C$32)=1,L2,(IF(COUNTIF(M3:M34,$C$32)=1,M2,(IF(COUNTIF(N3:N34,$C$32)=1,N2,IF(COUNTIF(K3:K34,$C$32)=1,K2,""))))))
Not the prettiest solution but it'll work. Then you can just repeat this for however many columns you want
=IF(COUNTIF(L3:L34,$C$32)=1,L2,(IF(COUNTIF(M3:M34,$C$32)=1,M2,(IF(COUNTIF(N3:N34,$C$32)=1,N2,IF(COUNTIF(K3:K34,$C$32)=1,K2,""))))))
Upvote
0
Similar threads
