Need to Lookup with Row and Column

[Kulo5856]

Hi Everyone,
I need to get data from row and column which is very urgent
Kindly solve this issue and do the needful

CC.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		A	B	C	D	E	J
2		Karnataka, Tamil Nadu & Pondicherry, Andhra Pradesh, Kerala	Chennai	Delhi, New Delhi, Gaziabad, Guragon, Faridabad, Noida, Haryana, Uttar Pradesh & Rajasthan	Punjab, Chandigarh, Uttarakhand & Himachal Pradesh	West Bengal, Bihar, Jharkhand & Orissa, Chinchwad, Pimpri, Maharashtra, Goa, Ahmedabad, Gujarat, Daman & Diu, Dadra & Nagar Havelli,Chattisgarh & Madhya Pradesh	Guwahati & North East, Jammu & Kashmir
3	1	232	216	298	315	265	348
4	2	232	216	298	315	265	348		Zone	A
5	3	232	216	298	315	265	348		Weight	2	If I enter weight in this cell
6	4	232	216	298	315	265	348
7	5	232	216	298	315	265	348		Charges		I need to get the value from particular zone
8	6	232	216	298	315	265	348
9	7	232	216	298	315	265	348				also if if weight exceeds above 71 kg need to multiply with the value
10	8	232	216	298	315	265	348
11	9	232	216	299	315	268	354
12	10	243	223	317	335	283	379
13	11	258	235	339	359	302	407
14	12	273	248	361	382	320	435
15	13	287	260	383	406	339	464
16	14	302	273	405	430	358	492
17	15	316	285	427	454	376	520
18	16	331	298	449	477	395	548
19	17	346	310	471	501	413	576
20	18	360	323	493	525	432	604
21	19	375	336	515	549	450	633
22	20	389	348	537	572	469	661
23	21	404	361	559	596	488	689
24	22	419	373	581	620	506	717
25	23	433	386	603	644	525	745
26	24	448	398	625	667	543	773
27	25	462	411	647	691	562	802
28	26	477	423	669	715	581	830
29	27	492	436	691	739	599	858
30	28	506	448	713	762	618	886
31	29	521	461	735	786	636	914
32	30	535	473	757	810	655	943
33	31	550	486	779	834	673	971
34	32	565	499	801	857	692	999
35	33	579	511	823	881	711	1027
36	34	594	524	845	905	729	1055
37	35	608	536	867	929	748	1083
38	36	623	549	889	952	766	1112
39	37	638	561	911	976	785	1140
40	38	652	574	932	1000	804	1168
41	39	667	586	954	1024	822	1196
42	40	681	599	976	1047	841	1224
43	41	696	611	998	1071	859	1252
44	42	711	624	1020	1095	878	1281
45	43	725	636	1042	1118	897	1309
46	44	740	649	1064	1142	915	1337
47	45	754	662	1086	1166	934	1365
48	46	769	674	1108	1190	952	1393
49	47	784	687	1130	1213	971	1421
50	48	798	699	1152	1237	989	1450
51	49	813	712	1174	1261	1008	1478
52	50	827	724	1196	1285	1027	1506
53	51	842	737	1218	1308	1045	1534
54	52	857	749	1240	1332	1064	1562
55	53	871	762	1262	1356	1082	1590
56	54	886	774	1284	1380	1101	1619
57	55	900	787	1306	1403	1120	1647
58	56	915	799	1328	1427	1138	1675
59	57	930	812	1350	1451	1157	1703
60	58	944	825	1372	1475	1175	1731
61	59	959	837	1394	1498	1194	1760
62	60	974	850	1416	1522	1212	1788
63	61	988	862	1438	1546	1231	1816
64	62	1003	875	1460	1570	1250	1844
65	63	1017	887	1482	1593	1268	1872
66	64	1032	900	1504	1617	1287	1900
67	65	1047	912	1526	1641	1305	1929
68	66	1061	925	1548	1665	1324	1957
69	67	1076	937	1570	1688	1343	1985
70	68	1090	950	1592	1712	1361	2013
71	69	1105	962	1614	1736	1380	2041
72	70	1120	975	1636	1760	1398	2069
73
74	71.0 - 99.0	14.60	12.54	21.98	23.75	18.59	28.17
75	100.0 - 299.0	14.60	12.54	21.98	23.75	18.59	28.17
76	300.0 - 499.0	14.60	12.54	21.98	23.75	18.59	28.17
77	500.0 - 999.0	14.60	12.54	21.98	23.75	18.59	28.17
78	1,000.0 - 9,999.0	14.60	12.54	21.98	23.75	18.59	28.17
79
80
Sheet1

 

[Kulo5856]

This is because, in your data you have used 71.0 - 99.0 and so on for all weights above 70
View attachment 26358
Therefore the formula does not recognize your weight as 71 or 99 and so on.
You have to use exact search criteria as mentioned in your data, e.g. 71.0 - 99.0 or 300.0 - 499.0 and so on.

View attachment 26359

for instance try using the weight 100.0 - 299.0 and let me know what happens?

i tried sir, but i need the formula which shows the value based on our weight range like 82 kg, 112 kg etc..
Because value will change as per weight and Zone wise.

 

[fadee2]

If zone values donot change above 71kg and your data "only" looks like the one as shown above then, use

=IF(J5>70,(INDEX($B$1:$G$78,MATCH("Above 71 Kg",$A$1:$A$78,0),MATCH($J$4,$B$1:$G$1,0))*70),INDEX($B$1:$G$78,MATCH($J$5,$A$1:$A$78,0),MATCH($J$4,$B$1:$G$1,0)))

this looksup the value "Above 71 kg" and returns respective values.

 

[Kulo5856]

View attachment 26363
If zone values donot change above 71kg and your data "only" looks like the one as shown above then, use

=IF(J5>70,(INDEX($B$1:$G$78,MATCH("Above 71 Kg",$A$1:$A$78,0),MATCH($J$4,$B$1:$G$1,0))*70),INDEX($B$1:$G$78,MATCH($J$5,$A$1:$A$78,0),MATCH($J$4,$B$1:$G$1,0)))

this looksup the value "Above 71 kg" and returns respective values.

Thanks sir its works fine..

 

[fadee2]

Your welcome and thanks for the followup.