Formula to combine, if +index match

[gaftalik]

Hi everyone,

I have the below table where as:


B4:G4 is the price per 2kgs (A4) for each city area
B6 is the additional price for each additional 2 kgs for every city.

Having A10:A27 as different weights, how can i look each weight of this range in its respective city and calculate the cost automatically with additional charges accordingly.

I have shown for CITY1 in B10:B27 what the results should be.

I appreciate very much your help.

Test.xls
	A	B	C	D	E	F	G	H
1
2
3	WEIGHTKG	CITY1	CITY2	CITY3	CITY4	CITY5	CITY6
4	2	$4.00	$4.00	$5.00	$5.00	$6.00	$6.00
5
6	Additionalchargeforeach2kgs	$2.50	$2.50	$2.50	$2.50	$2.50	$2.50
7
8
9	WEIGHTKG	CITY1	CITY2	CITY3	CITY4	CITY5	CITY6
10	0.41	4
11	0.72	4
12	1.13	4
13	1.62	4
14	2.88	6.5
15	3.09	6.5
16	3.82	6.5
17	0.54	4
18	0.86	4
19	1.22	4
20	1.44	4
21	2.16	6.5
22	2.34	6.5
23	1.91	4
24	2.86	6.5
25	2.75	6.5
26	4.12	9
27	6.18	11.5
28
29
30
Sheet1

 

[gaftalik]

now it's working good, thank you very much !

 

[RichardMGreen]

No problem.
Glad we got there in the end.