Finding the correct formula for multiple ranges of zip codes to determine shipping cost.

[bmccan20]

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?

 

[kang23]

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?

Can you provide an example?
Seems like a pretty straight forward use of vlookup or index/match functions

 

[bmccan20]

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.

 

[kang23]

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)

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Zip[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Zip[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]404[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]404[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]600[/TD]
[/TR]
</tbody>[/TABLE]

 

[bmccan20]

 

[kang23]

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,""))))))

 

[bmccan20]

Thank you so much! I can't wait to get this working!

 

[DanteAmor]

Try with this formula

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					SALES						Delivery Charges
2											100		110		120
3											44901		45501		46701
4											44902		45502		46702
5											44903		45503		46703
6											44904		45504		46704
7											44905		45505		46705
8											44906		45506		46706
9											44907		45507		46707
10													45508		46708
11													45509		46709
12															46710
30															46728
31	DELIVERY		ZIP CODE												46729
32			45505		110
Sheet

 

[bmccan20]

Works great thanks!

 

[bmccan20]

This is a really cool approach and works great, thank you!