How toVlookup multiple values and match

[RAJESH1960]

I have these sheets which have more than 5000 rows to match. I have to do it manually.
I am able to VLOOKUP only single column values but not multiple columns.

I want to know how to VLOOKUP in column C, the value of column B with the same value in G. The value in column A should also match with column F.

If columns A & B are equal to columns F & G then it should display the amount, else it should display “Not Available”

And again, If columns F & G are equal to columns A & B then it should display the amount, else it should display “Not Available”

P.S. I want to use only formula in my sheets.

Query.xlsx
	A	B	C	D	E	F	G	H
1	Particulars A	Gross Total A	Not Matching with B			Particulars B	Gross Total B	Not Matching with A
2	27AAFCA7018N1ZE	0	#N/A			29AAJFD5413B1ZR	140.00	#N/A
3	27AAHFH0417M1ZC	1.12	#N/A			29AADFG7707M1ZY	199.00	199
4	29AAGCR4375J1ZU	76	#N/A			24AAHCS4768D4Z7	236.00	236
5	29AAACD4979N1ZW	92.4	#N/A			29AADCF1911H1ZU	288.00	288
6	29AAACD4979N1ZW	94.5	#N/A			29AADCF1911H1ZU	355.00	355
7	29AAACD4979N1ZW	95.55	#N/A			24AAHCS4768D4Z7	420.00	#N/A
8	07AAACP0165G1ZR	118	#N/A			24AAHCS4768D4Z7	443.00	#N/A
9	29AAACD4979N1ZW	121.8	#N/A			24AAHCS4768D4Z7	489.00	489
10	29AAACD4979N1ZW	135.45	#N/A			29AADCF1911H1ZU	555.00	#N/A
11	07AACCI5713M1ZF	149	140			29AADCF1911H1ZU	567.00	567
12	29AAVCS6045D2ZV	168.74	140			29AADCF1911H1ZU	578.00	578
13	29AAACD4979N1ZW	178.5	140			29AADCF1911H1ZU	586.00	586
14	29AADFG7707M1ZY	199	199			29AAJFD5413B1ZR	588.00	588
15	29AACFM5833D1ZD	200	199			24AAHCS4768D4Z7	591.00	591
16	29AACFM5833D1ZD	200	199			29AADFG7707M1ZY	599.00	#N/A
17	29AACFM5833D1ZD	200	199			29AADCF1911H1ZU	607.00	607
18	29AACFM5833D1ZD	200	199			29AADFG7707M1ZY	608.00	#N/A
19	29AACFM5833D1ZD	200	199			24AAHCS4768D4Z7	609.00	#N/A
20	29AACFM5833D1ZD	200	199			29AAJFD5413B1ZR	696.00	696
21	27AAQCS4259Q1ZA	220	199			29AAJFD5413B1ZR	696.00	696
22	07AAACP0165G1ZR	236	236			24AAHCS4768D4Z7	739.20	#N/A
23	29AADCF1911H1ZU	288	288			29AADCF1911H1ZU	749.00	#N/A
24	29AADFG7707M1ZY	307	288			29AADCF1911H1ZU	756.00	756
25	29AACFM5833D1ZD	330	288			29AADFG7707M1ZY	759.00	#N/A
26	29AACFM5833D1ZD	350	288			29AADFG7707M1ZY	809.00	809
27	29AACFM5833D1ZD	350	288			29AAJFD5413B1ZR	841.00	#N/A
28	29AADCF1911H1ZU	355	355			24AAHCS4768D4Z7	870.00	#N/A
29	07AAACP0165G1ZR	397	355			24AAHCS4768D4Z7	920.00	920
30	29AACFM5833D1ZD	400	355			06AAECH5372H1ZK	929.00	#N/A
31	29AACFM5833D1ZD	400	355			29AADCF1911H1ZU	943.00	943
32	29AACFM5833D1ZD	400	355			27AABPA0901H1ZI	960.00	960
33	06AABCJ8820B1ZY	428.81	420			29AAJFD5413B1ZR	960.00	960
34	24AAHCS4768D4Z7	489	489			29AADFG7707M1ZY	1,040.00	#N/A
35	29AADFG7707M1ZY	528	489			27AAEFB7230K1ZG	1,050.00	1050
36	29AADFG7707M1ZY	528	489			24AAHCS4768D4Z7	1,052.00	#N/A
37	29AADFG7707M1ZY	543	489			29AADFG7707M1ZY	1,053.00	1053
38	29AADCF1911H1ZU	567	567			29AAJFD5413B1ZR	1,071.00	#N/A
39	29AADCF1911H1ZU	578	578			29AAJFD5413B1ZR	1,071.00	#N/A
40	29AADCF1911H1ZU	586	586			29AADFG7707M1ZY	1,074.00	1074
41	29AAJFD5413B1ZR	588	588			29AAJFD5413B1ZR	1,080.00	1080
42	24AAACP0165G1ZV	590	588			24AAHCS4768D4Z7	1,100.00	1100
43	27AAACP0165G3ZN	590	588			24AAHCS4768D4Z7	1,131.00	1131
44	24AAACP0165G1ZV	590	588			06AAECH5372H1ZK	1,147.00	1147
45	24AAHCS4768D4Z7	591	591			29AADFG7707M1ZY	1,182.00	1182
46	29AACFM5833D1ZD	600	599			29AEEPB1993F1ZK	1,239.00	#N/A
47	27AAQCS4259Q1ZA	605	599			29AADFG7707M1ZY	1,255.00	#N/A
48	29AADCF1911H1ZU	607	607			27AABPA0901H1ZI	1,260.00	1260
49	29AACFM5833D1ZD	650	609			29AADCF1911H1ZU	1,267.00	1267
50	29AAJFD5413B1ZR	696	696			29AADCF1911H1ZU	1,270.00	1270
51	07AAACP0165G1ZR	708	696			29AAJFD5413B1ZR	1,279.00	#N/A
52	07AAACP0165G1ZR	721.04	696			29AADFG7707M1ZY	1,284.00	1284
53	24AAHCS4768D4Z7	739	696			24AAHCS4768D4Z7	1,329.00	#N/A
54	29AAMCA7504L1Z8	745	739.2			29AAVCS6045D2ZV	1,370.00	#N/A
55	29AADCF1911H1ZU	756	756			29AADCF1911H1ZU	1,401.00	1401
56	29AABCV3609C1ZJ	760	759			27ADOPA2609J1ZL	1,411.00	#N/A
57	27AAJCS7347C1Z7	793	759			27AABPA0901H1ZI	1,428.00	#N/A
58	29AADFG7707M1ZY	809	809			29AADFG7707M1ZY	1,476.00	#N/A
59	29AADFG7707M1ZY	809	809			27AADFF8018N1Z3	1,480.00	#N/A
60	29AABCV3609C1ZJ	820	809			29AADFG7707M1ZY	1,502.00	1502
61	29AADCH8879C1Z5	825.52	809			29AADCF1911H1ZU	1,520.00	1520
62	29AABCV3609C1ZJ	850	841			06AAECH5372H1ZK	1,549.00	#N/A
63	29AACCF0683K1ZD	853.47	841			06AAECH5372H1ZK	1,549.00	#N/A
64	08AAACD4979N1Z0	861	841			29AADCF1911H1ZU	1,559.00	1559
65	29AAACB2894G1ZJ	878.33	870			29AADCF1911H1ZU	1,571.00	1571
66	29AABCV3609C1ZJ	900	870			24AAHCS4768D4Z7	1,584.00	#N/A
67	29AABCV3609C1ZJ	910	870			29AADCF1911H1ZU	1,617.00	1617
68	29AABCV3609C1ZJ	920	920			29AAJFD5413B1ZR	1,624.00	1624
69	27AABCV3609C1ZN	940	929			29AADCF1911H1ZU	1,630.00	1630
70	29AAACB2894G1ZJ	942.82	929			29AAJFD5413B1ZR	1,639.00	1639
71	29AADCF1911H1ZU	943	943			29AADCF1911H1ZU	1,644.00	1644
72	29AAJFD5413B1ZR	960	960			24AAHCS4768D2Z9	1,680.00	#N/A
73	33AAGCT1928B1ZR	960.96	960			27AADFF8018N1Z3	1,756.00	#N/A
74	09AAACV1559Q1ZO	980	960			27AAEFB7230K1ZG	1,772.00	#N/A
75	24ACNPT0585G1ZB	1043	1040			29AADFG7707M1ZY	1,779.00	1779
76	29AAACP0165G1ZL	1044.3	1040			29AADCF1911H1ZU	1,811.00	#N/A
77	27AAEFB7230K1ZG	1050	1050			24AAHCS4768D4Z7	1,837.00	#N/A
78	29AADFG7707M1ZY	1053	1053			29AADCF1911H1ZU	1,841.00	1841
79	29AABCV3609C1ZJ	1060	1053			29AAJFD5413B1ZR	1,856.00	1856
80	29AAACB2894G1ZJ	1060.82	1053			06AAECH5372H1ZK	1,859.00	#N/A
81	29AAACB2894G1ZJ	1060.82	1053			24AAHCS4768D4Z7	1,862.00	#N/A
82	29AAACB2894G1ZJ	1060.82	1053			27AADFF8018N1Z3	1,907.00	#N/A
83	29AADFG7707M1ZY	1074	1074			27ABKFM1457E1Z9	1,932.00	#N/A
84	29AADFG7707M1ZY	1074	1074			24AAHCS4768D4Z7	1,947.00	1947
85	29AABCV3609C1ZJ	1080	1080			29AAJFD5413B1ZR	1,972.00	#N/A
86	29AAJFD5413B1ZR	1080	1080			29ABGPG5690M1Z2	2,006.00	#N/A
87	24AABCV3609C1ZT	1100	1100			29ABGPG5690M1Z2	2,006.00	#N/A
88	29AABCV3609C1ZJ	1110	1100			29AAJFD5413B1ZR	2,030.00	#N/A
89	29AABCV3609C1ZJ	1120	1100			27AABPA0901H1ZI	2,105.00	#N/A
90	29AABCV3609C1ZJ	1130	1100			24AAHCS4768D4Z7	2,171.00	#N/A
91	24AAHCS4768D4Z7	1131	1131			27AABPA0901H1ZI	2,174.00	#N/A
92	08AAACD4979N1Z0	1138.2	1131			24ACWFS1439D1ZZ	2,185.00	#N/A
93	29AABCV3609C1ZJ	1140	1131			24AAHCS4768D4Z7	2,208.00	#N/A
94	06AAECH5372H1ZK	1147	1147			29AADCF1911H1ZU	2,209.00	#N/A
95	08AAACD4979N1Z0	1157.1	1147			27DPXPS7820K1ZT	2,213.00	#N/A
96	29AABCV3609C1ZJ	1160	1147			27DPXPS7820K1ZT	2,213.00	#N/A
97	29AABCV3609C1ZJ	1170	1147			27DPXPS7820K1ZT	2,213.00	#N/A
98	29AADFG7707M1ZY	1182	1182			27ADOPA2609J1ZL	2,231.00	#N/A
99	29AADCF1911H1ZU	1192	1182			24AAHCS4768D4Z7	2,296.00	#N/A
100	29AABCV3609C1ZJ	1220	1182			29AAJFD5413B1ZR	2,320.00	#N/A
101	29AABCV3609C1ZJ	1230	1182			24AAHCS4768D4Z7	2,360.00	#N/A
102	29AABCV3609C1ZJ	1230	1182			29AAJFD5413B1ZR	2,380.00	#N/A
103	24AABCV3609C1ZT	1260	1260			24AAHCS4768D4Z7	2,394.00	#N/A
104	27AELPN4350H1ZC	1266	1260			27AABPA0901H1ZI	2,409.00	#N/A
105	29AADCF1911H1ZU	1267	1267			29AADCF1911H1ZU	2,421.00	#N/A
106	29AABCV3609C1ZJ	1270	1270			27AABPA0901H1ZI	2,462.00	#N/A
107	29AADCF1911H1ZU	1270	1270			24AAHCS4768D4Z7	2,494.00	#N/A
108	29AABCV3609C1ZJ	1280	1279			24AAHCS4768D4Z7	2,494.00	#N/A
109	29AADFG7707M1ZY	1284	1284			06AAECH5372H1ZK	2,514.00	#N/A
110	29AABCV3609C1ZJ	1290	1284			06AAECH5372H1ZK	2,514.00	#N/A
111	29AABCV3609C1ZJ	1290	1284			27AADFF8018N1Z3	2,520.00	#N/A
112	27AAJCS7347C1Z7	1322	1284			29AADFG7707M1ZY	2,600.00	#N/A
113	29AABCV3609C1ZJ	1350	1329			29AADCF1911H1ZU	2,604.00	#N/A
114	29AABCV3609C1ZJ	1360	1329			29AAJFD5413B1ZR	2,618.00	#N/A
115	29AADCF1911H1ZU	1401	1401			29AADCF1911H1ZU	2,633.00	#N/A
116	29AADCF1911H1ZU	1436	1428			24AAHCS4768D4Z7	2,640.00	#N/A
117	29AACFM5833D1ZD	1500	1480			29AADCF1911H1ZU	2,661.00	#N/A
118	29AADFG7707M1ZY	1502	1502			27AADFF8018N1Z3	2,661.00	#N/A
119	29AADCF1911H1ZU	1520	1520			24AAHCS4768D4Z7	2,673.00	#N/A
120	29AACCC8157H1ZE	1534	1520			29AADCF1911H1ZU	2,722.00	#N/A
121	29AABCV3609C1ZJ	1550	1549			27AADFF8018N1Z3	2,740.00	#N/A
122	29AADCF1911H1ZU	1559	1559			24AAHCS4768D4Z7	2,742.00	#N/A
123	29AADCF1911H1ZU	1571	1571			24AAHCS4768D4Z7	2,769.00	#N/A
124	29AAACP0165G1ZL	1587.39	1584			24AAHCS4768D4Z7	2,785.00	#N/A
125	29AABCV3609C1ZJ	1601	1584			29AADFG7707M1ZY	2,787.00	#N/A
126	29AADCF1911H1ZU	1617	1617			33ABGFM1080P1Z2	2,793.00	#N/A
127	29AAJFD5413B1ZR	1624	1624			29AADFG7707M1ZY	2,811.00	#N/A
128	29AADCF1911H1ZU	1630	1630			27AADFF8018N1Z3	2,835.00	#N/A
129	29AAJFD5413B1ZR	1639	1639			24AAHCS4768D4Z7	2,894.00	#N/A
130	29AADCF1911H1ZU	1644	1644			29AAJFD5413B1ZR	2,900.00	#N/A
131	08AAACD4979N1Z0	1669.5	1644			29AAJFD5413B1ZR	2,900.00	#N/A
132	08AAACD4979N1Z0	1670.55	1644			29CKEPB2743P1ZU	2,912.00	#N/A
133	29AADCF1911H1ZU	1696	1680			29AADCF1911H1ZU	2,930.00	#N/A
134	29AABCV3609C1ZJ	1700	1680			29AADCF1911H1ZU	2,931.00	#N/A
135	09AABCV3609C1ZL	1700	1680			29AADCF1911H1ZU	2,940.00	#N/A
136	29AAICA3918J1ZE	1731.77	1680			24BGZPS1156F1Z0	2,950.00	#N/A
137	29AADFG7707M1ZY	1779	1779			29AAJFD5413B1ZR	2,975.00	#N/A
138	09AABCV3609C1ZL	1828	1811			24AAHCS4768D4Z7	2,976.00	#N/A
139	29AADCF1911H1ZU	1841	1841			29APHPS1047M2ZC	3,000.00	#N/A
140	29AAJFD5413B1ZR	1856	1856			27AADFF8018N1Z3	3,021.00	#N/A
141	09AABCV3609C1ZL	1880	1862			29AADCF1911H1ZU	3,024.00	#N/A
142	29AABCV3609C1ZJ	1920	1907			29AADFG7707M1ZY	3,039.00	#N/A
143	24AAHCS4768D4Z7	1947	1947			27AABPA0901H1ZI	3,060.00	#N/A
144	29AAACS9735K1ZR	1970	1947			24AAHCS4768D4Z7	3,106.00	#N/A
145	29AADCF1911H1ZU	1999	1972			27DPXPS7820K1ZT	3,186.00	#N/A
146	06AALCA0171E1Z3	1999	1972			27DPXPS7820K1ZT	3,186.00	#N/A
Match

Cell Formulas
Range	Formula
C2:C146	C2	=VLOOKUP(B2,$G$2:$G$146,1)
H2:H146	H2	=VLOOKUP(G2,$B$2:$B$146,1,0)

 

[Peter_SSs]

Is this what you mean?
If not please give a small set of sample data with XL2BB with the expected results entered in manually.

21 01 30.xlsm
	A	B	C	D	E	F	G
1	Particulars A	Gross Total A	Not Matching with B			Particulars B	Gross Total B
2	27AAFCA7018N1ZE	0	Not Available			29AAJFD5413B1ZR	140
3	27AAHFH0417M1ZC	1.12	Not Available			29AADFG7707M1ZY	199
4	29AAGCR4375J1ZU	76	Not Available			24AAHCS4768D4Z7	236
5	29AAACD4979N1ZW	92.4	Not Available			29AADCF1911H1ZU	288
6	29AAACD4979N1ZW	94.5	Not Available			29AADCF1911H1ZU	355
7	29AAACD4979N1ZW	95.55	Not Available			24AAHCS4768D4Z7	420
8	07AAACP0165G1ZR	118	Not Available			24AAHCS4768D4Z7	443
9	29AAACD4979N1ZW	121.8	Not Available			24AAHCS4768D4Z7	489
10	29AAACD4979N1ZW	135.45	Not Available			29AADCF1911H1ZU	555
11	07AACCI5713M1ZF	149	Not Available			29AADCF1911H1ZU	567
12	29AAVCS6045D2ZV	168.74	Not Available			29AADCF1911H1ZU	578
13	29AAACD4979N1ZW	178.5	Not Available			29AADCF1911H1ZU	586
14	29AADFG7707M1ZY	199	199			29AAJFD5413B1ZR	588
15	29AACFM5833D1ZD	200	Not Available			24AAHCS4768D4Z7	591
16	29AACFM5833D1ZD	200	Not Available			29AADFG7707M1ZY	599
17	29AACFM5833D1ZD	200	Not Available			29AADCF1911H1ZU	607
18	29AACFM5833D1ZD	200	Not Available			29AADFG7707M1ZY	608
19	29AACFM5833D1ZD	200	Not Available			24AAHCS4768D4Z7	609
20	29AACFM5833D1ZD	200	Not Available			29AAJFD5413B1ZR	696
21	27AAQCS4259Q1ZA	220	Not Available			29AAJFD5413B1ZR	696
22	07AAACP0165G1ZR	236	Not Available			24AAHCS4768D4Z7	739.2
23	29AADCF1911H1ZU	288	288			29AADCF1911H1ZU	749
24	29AADFG7707M1ZY	307	Not Available			29AADCF1911H1ZU	756
25	29AACFM5833D1ZD	330	Not Available			29AADFG7707M1ZY	759
26	29AACFM5833D1ZD	350	Not Available			29AADFG7707M1ZY	809
27	29AACFM5833D1ZD	350	Not Available			29AAJFD5413B1ZR	841
28	29AADCF1911H1ZU	355	355			24AAHCS4768D4Z7	870
29	07AAACP0165G1ZR	397	Not Available			24AAHCS4768D4Z7	920
30	29AACFM5833D1ZD	400	Not Available			06AAECH5372H1ZK	929
31	29AACFM5833D1ZD	400	Not Available			29AADCF1911H1ZU	943
32	29AACFM5833D1ZD	400	Not Available			27AABPA0901H1ZI	960
33	06AABCJ8820B1ZY	428.81	Not Available			29AAJFD5413B1ZR	960
34	24AAHCS4768D4Z7	489	489			29AADFG7707M1ZY	1040
35	29AADFG7707M1ZY	528	Not Available			27AAEFB7230K1ZG	1050
36	29AADFG7707M1ZY	528	Not Available			24AAHCS4768D4Z7	1052
37	29AADFG7707M1ZY	543	Not Available			29AADFG7707M1ZY	1053
38	29AADCF1911H1ZU	567	567			29AAJFD5413B1ZR	1071
39	29AADCF1911H1ZU	578	578			29AAJFD5413B1ZR	1071
40	29AADCF1911H1ZU	586	586			29AADFG7707M1ZY	1074
41	29AAJFD5413B1ZR	588	588			29AAJFD5413B1ZR	1080
42	24AAACP0165G1ZV	590	Not Available			24AAHCS4768D4Z7	1100
43	27AAACP0165G3ZN	590	Not Available			24AAHCS4768D4Z7	1131
44	24AAACP0165G1ZV	590	Not Available			06AAECH5372H1ZK	1147
45	24AAHCS4768D4Z7	591	591			29AADFG7707M1ZY	1182
46	29AACFM5833D1ZD	600	Not Available			29AEEPB1993F1ZK	1239
47	27AAQCS4259Q1ZA	605	Not Available			29AADFG7707M1ZY	1255
48	29AADCF1911H1ZU	607	607			27AABPA0901H1ZI	1260
49	29AACFM5833D1ZD	650	Not Available			29AADCF1911H1ZU	1267
50	29AAJFD5413B1ZR	696	696			29AADCF1911H1ZU	1270
51	07AAACP0165G1ZR	708	Not Available			29AAJFD5413B1ZR	1279
52	07AAACP0165G1ZR	721.04	Not Available			29AADFG7707M1ZY	1284
53	24AAHCS4768D4Z7	739	Not Available			24AAHCS4768D4Z7	1329
54	29AAMCA7504L1Z8	745	Not Available			29AAVCS6045D2ZV	1370
55	29AADCF1911H1ZU	756	756			29AADCF1911H1ZU	1401
56	29AABCV3609C1ZJ	760	Not Available			27ADOPA2609J1ZL	1411
57	27AAJCS7347C1Z7	793	Not Available			27AABPA0901H1ZI	1428
58	29AADFG7707M1ZY	809	809			29AADFG7707M1ZY	1476
59	29AADFG7707M1ZY	809	809			27AADFF8018N1Z3	1480
60	29AABCV3609C1ZJ	820	Not Available			29AADFG7707M1ZY	1502
61	29AADCH8879C1Z5	825.52	Not Available			29AADCF1911H1ZU	1520
62	29AABCV3609C1ZJ	850	Not Available			06AAECH5372H1ZK	1549
63	29AACCF0683K1ZD	853.47	Not Available			06AAECH5372H1ZK	1549
64	08AAACD4979N1Z0	861	Not Available			29AADCF1911H1ZU	1559
65	29AAACB2894G1ZJ	878.33	Not Available			29AADCF1911H1ZU	1571
66	29AABCV3609C1ZJ	900	Not Available			24AAHCS4768D4Z7	1584
67	29AABCV3609C1ZJ	910	Not Available			29AADCF1911H1ZU	1617
68	29AABCV3609C1ZJ	920	Not Available			29AAJFD5413B1ZR	1624
69	27AABCV3609C1ZN	940	Not Available			29AADCF1911H1ZU	1630
70	29AAACB2894G1ZJ	942.82	Not Available			29AAJFD5413B1ZR	1639
71	29AADCF1911H1ZU	943	943			29AADCF1911H1ZU	1644
72	29AAJFD5413B1ZR	960	960			24AAHCS4768D2Z9	1680
73	33AAGCT1928B1ZR	960.96	Not Available			27AADFF8018N1Z3	1756
74	09AAACV1559Q1ZO	980	Not Available			27AAEFB7230K1ZG	1772
75	24ACNPT0585G1ZB	1043	Not Available			29AADFG7707M1ZY	1779
76	29AAACP0165G1ZL	1044.3	Not Available			29AADCF1911H1ZU	1811
77	27AAEFB7230K1ZG	1050	1050			24AAHCS4768D4Z7	1837
78	29AADFG7707M1ZY	1053	1053			29AADCF1911H1ZU	1841
79	29AABCV3609C1ZJ	1060	Not Available			29AAJFD5413B1ZR	1856
80	29AAACB2894G1ZJ	1060.82	Not Available			06AAECH5372H1ZK	1859
81	29AAACB2894G1ZJ	1060.82	Not Available			24AAHCS4768D4Z7	1862
82	29AAACB2894G1ZJ	1060.82	Not Available			27AADFF8018N1Z3	1907
83	29AADFG7707M1ZY	1074	1074			27ABKFM1457E1Z9	1932
84	29AADFG7707M1ZY	1074	1074			24AAHCS4768D4Z7	1947
85	29AABCV3609C1ZJ	1080	Not Available			29AAJFD5413B1ZR	1972
86	29AAJFD5413B1ZR	1080	1080			29ABGPG5690M1Z2	2006
87	24AABCV3609C1ZT	1100	Not Available			29ABGPG5690M1Z2	2006
88	29AABCV3609C1ZJ	1110	Not Available			29AAJFD5413B1ZR	2030
89	29AABCV3609C1ZJ	1120	Not Available			27AABPA0901H1ZI	2105
90	29AABCV3609C1ZJ	1130	Not Available			24AAHCS4768D4Z7	2171
91	24AAHCS4768D4Z7	1131	1131			27AABPA0901H1ZI	2174
92	08AAACD4979N1Z0	1138.2	Not Available			24ACWFS1439D1ZZ	2185
93	29AABCV3609C1ZJ	1140	Not Available			24AAHCS4768D4Z7	2208
94	06AAECH5372H1ZK	1147	1147			29AADCF1911H1ZU	2209
95	08AAACD4979N1Z0	1157.1	Not Available			27DPXPS7820K1ZT	2213
96	29AABCV3609C1ZJ	1160	Not Available			27DPXPS7820K1ZT	2213
97	29AABCV3609C1ZJ	1170	Not Available			27DPXPS7820K1ZT	2213
98	29AADFG7707M1ZY	1182	1182			27ADOPA2609J1ZL	2231
99	29AADCF1911H1ZU	1192	Not Available			24AAHCS4768D4Z7	2296
100	29AABCV3609C1ZJ	1220	Not Available			29AAJFD5413B1ZR	2320
101	29AABCV3609C1ZJ	1230	Not Available			24AAHCS4768D4Z7	2360
102	29AABCV3609C1ZJ	1230	Not Available			29AAJFD5413B1ZR	2380
103	24AABCV3609C1ZT	1260	Not Available			24AAHCS4768D4Z7	2394
104	27AELPN4350H1ZC	1266	Not Available			27AABPA0901H1ZI	2409
105	29AADCF1911H1ZU	1267	1267			29AADCF1911H1ZU	2421
106	29AABCV3609C1ZJ	1270	Not Available			27AABPA0901H1ZI	2462
107	29AADCF1911H1ZU	1270	1270			24AAHCS4768D4Z7	2494
108	29AABCV3609C1ZJ	1280	Not Available			24AAHCS4768D4Z7	2494
109	29AADFG7707M1ZY	1284	1284			06AAECH5372H1ZK	2514
110	29AABCV3609C1ZJ	1290	Not Available			06AAECH5372H1ZK	2514
111	29AABCV3609C1ZJ	1290	Not Available			27AADFF8018N1Z3	2520
112	27AAJCS7347C1Z7	1322	Not Available			29AADFG7707M1ZY	2600
113	29AABCV3609C1ZJ	1350	Not Available			29AADCF1911H1ZU	2604
114	29AABCV3609C1ZJ	1360	Not Available			29AAJFD5413B1ZR	2618
115	29AADCF1911H1ZU	1401	1401			29AADCF1911H1ZU	2633
116	29AADCF1911H1ZU	1436	Not Available			24AAHCS4768D4Z7	2640
117	29AACFM5833D1ZD	1500	Not Available			29AADCF1911H1ZU	2661
118	29AADFG7707M1ZY	1502	1502			27AADFF8018N1Z3	2661
119	29AADCF1911H1ZU	1520	1520			24AAHCS4768D4Z7	2673
120	29AACCC8157H1ZE	1534	Not Available			29AADCF1911H1ZU	2722
121	29AABCV3609C1ZJ	1550	Not Available			27AADFF8018N1Z3	2740
122	29AADCF1911H1ZU	1559	1559			24AAHCS4768D4Z7	2742
123	29AADCF1911H1ZU	1571	1571			24AAHCS4768D4Z7	2769
124	29AAACP0165G1ZL	1587.39	Not Available			24AAHCS4768D4Z7	2785
125	29AABCV3609C1ZJ	1601	Not Available			29AADFG7707M1ZY	2787
126	29AADCF1911H1ZU	1617	1617			33ABGFM1080P1Z2	2793
127	29AAJFD5413B1ZR	1624	1624			29AADFG7707M1ZY	2811
128	29AADCF1911H1ZU	1630	1630			27AADFF8018N1Z3	2835
129	29AAJFD5413B1ZR	1639	1639			24AAHCS4768D4Z7	2894
130	29AADCF1911H1ZU	1644	1644			29AAJFD5413B1ZR	2900
131	08AAACD4979N1Z0	1669.5	Not Available			29AAJFD5413B1ZR	2900
132	08AAACD4979N1Z0	1670.55	Not Available			29CKEPB2743P1ZU	2912
133	29AADCF1911H1ZU	1696	Not Available			29AADCF1911H1ZU	2930
134	29AABCV3609C1ZJ	1700	Not Available			29AADCF1911H1ZU	2931
135	09AABCV3609C1ZL	1700	Not Available			29AADCF1911H1ZU	2940
136	29AAICA3918J1ZE	1731.77	Not Available			24BGZPS1156F1Z0	2950
137	29AADFG7707M1ZY	1779	1779			29AAJFD5413B1ZR	2975
138	09AABCV3609C1ZL	1828	Not Available			24AAHCS4768D4Z7	2976
139	29AADCF1911H1ZU	1841	1841			29APHPS1047M2ZC	3000
140	29AAJFD5413B1ZR	1856	1856			27AADFF8018N1Z3	3021
141	09AABCV3609C1ZL	1880	Not Available			29AADCF1911H1ZU	3024
142	29AABCV3609C1ZJ	1920	Not Available			29AADFG7707M1ZY	3039
143	24AAHCS4768D4Z7	1947	1947			27AABPA0901H1ZI	3060
144	29AAACS9735K1ZR	1970	Not Available			24AAHCS4768D4Z7	3106
145	29AADCF1911H1ZU	1999	Not Available			27DPXPS7820K1ZT	3186
146	06AALCA0171E1Z3	1999	Not Available			27DPXPS7820K1ZT	3186
Lookup

Cell Formulas
Range	Formula
C2:C146	C2	=IF(COUNTIFS(F$2:F$146,A2,G$2:G$146,B2),B2,"Not Available")

 

[RAJESH1960]

Is this what you mean?
If not please give a small set of sample data with XL2BB with the expected results entered in manually.

21 01 30.xlsm
	A	B	C	D	E	F	G
1	Particulars A	Gross Total A	Not Matching with B			Particulars B	Gross Total B
2	27AAFCA7018N1ZE	0	Not Available			29AAJFD5413B1ZR	140
3	27AAHFH0417M1ZC	1.12	Not Available			29AADFG7707M1ZY	199
4	29AAGCR4375J1ZU	76	Not Available			24AAHCS4768D4Z7	236
5	29AAACD4979N1ZW	92.4	Not Available			29AADCF1911H1ZU	288
6	29AAACD4979N1ZW	94.5	Not Available			29AADCF1911H1ZU	355
7	29AAACD4979N1ZW	95.55	Not Available			24AAHCS4768D4Z7	420
8	07AAACP0165G1ZR	118	Not Available			24AAHCS4768D4Z7	443
9	29AAACD4979N1ZW	121.8	Not Available			24AAHCS4768D4Z7	489
10	29AAACD4979N1ZW	135.45	Not Available			29AADCF1911H1ZU	555
11	07AACCI5713M1ZF	149	Not Available			29AADCF1911H1ZU	567
12	29AAVCS6045D2ZV	168.74	Not Available			29AADCF1911H1ZU	578
13	29AAACD4979N1ZW	178.5	Not Available			29AADCF1911H1ZU	586
14	29AADFG7707M1ZY	199	199			29AAJFD5413B1ZR	588
15	29AACFM5833D1ZD	200	Not Available			24AAHCS4768D4Z7	591
16	29AACFM5833D1ZD	200	Not Available			29AADFG7707M1ZY	599
17	29AACFM5833D1ZD	200	Not Available			29AADCF1911H1ZU	607
18	29AACFM5833D1ZD	200	Not Available			29AADFG7707M1ZY	608
19	29AACFM5833D1ZD	200	Not Available			24AAHCS4768D4Z7	609
20	29AACFM5833D1ZD	200	Not Available			29AAJFD5413B1ZR	696
21	27AAQCS4259Q1ZA	220	Not Available			29AAJFD5413B1ZR	696
22	07AAACP0165G1ZR	236	Not Available			24AAHCS4768D4Z7	739.2
23	29AADCF1911H1ZU	288	288			29AADCF1911H1ZU	749
24	29AADFG7707M1ZY	307	Not Available			29AADCF1911H1ZU	756
25	29AACFM5833D1ZD	330	Not Available			29AADFG7707M1ZY	759
26	29AACFM5833D1ZD	350	Not Available			29AADFG7707M1ZY	809
27	29AACFM5833D1ZD	350	Not Available			29AAJFD5413B1ZR	841
28	29AADCF1911H1ZU	355	355			24AAHCS4768D4Z7	870
29	07AAACP0165G1ZR	397	Not Available			24AAHCS4768D4Z7	920
30	29AACFM5833D1ZD	400	Not Available			06AAECH5372H1ZK	929
31	29AACFM5833D1ZD	400	Not Available			29AADCF1911H1ZU	943
32	29AACFM5833D1ZD	400	Not Available			27AABPA0901H1ZI	960
33	06AABCJ8820B1ZY	428.81	Not Available			29AAJFD5413B1ZR	960
34	24AAHCS4768D4Z7	489	489			29AADFG7707M1ZY	1040
35	29AADFG7707M1ZY	528	Not Available			27AAEFB7230K1ZG	1050
36	29AADFG7707M1ZY	528	Not Available			24AAHCS4768D4Z7	1052
37	29AADFG7707M1ZY	543	Not Available			29AADFG7707M1ZY	1053
38	29AADCF1911H1ZU	567	567			29AAJFD5413B1ZR	1071
39	29AADCF1911H1ZU	578	578			29AAJFD5413B1ZR	1071
40	29AADCF1911H1ZU	586	586			29AADFG7707M1ZY	1074
41	29AAJFD5413B1ZR	588	588			29AAJFD5413B1ZR	1080
42	24AAACP0165G1ZV	590	Not Available			24AAHCS4768D4Z7	1100
43	27AAACP0165G3ZN	590	Not Available			24AAHCS4768D4Z7	1131
44	24AAACP0165G1ZV	590	Not Available			06AAECH5372H1ZK	1147
45	24AAHCS4768D4Z7	591	591			29AADFG7707M1ZY	1182
46	29AACFM5833D1ZD	600	Not Available			29AEEPB1993F1ZK	1239
47	27AAQCS4259Q1ZA	605	Not Available			29AADFG7707M1ZY	1255
48	29AADCF1911H1ZU	607	607			27AABPA0901H1ZI	1260
49	29AACFM5833D1ZD	650	Not Available			29AADCF1911H1ZU	1267
50	29AAJFD5413B1ZR	696	696			29AADCF1911H1ZU	1270
51	07AAACP0165G1ZR	708	Not Available			29AAJFD5413B1ZR	1279
52	07AAACP0165G1ZR	721.04	Not Available			29AADFG7707M1ZY	1284
53	24AAHCS4768D4Z7	739	Not Available			24AAHCS4768D4Z7	1329
54	29AAMCA7504L1Z8	745	Not Available			29AAVCS6045D2ZV	1370
55	29AADCF1911H1ZU	756	756			29AADCF1911H1ZU	1401
56	29AABCV3609C1ZJ	760	Not Available			27ADOPA2609J1ZL	1411
57	27AAJCS7347C1Z7	793	Not Available			27AABPA0901H1ZI	1428
58	29AADFG7707M1ZY	809	809			29AADFG7707M1ZY	1476
59	29AADFG7707M1ZY	809	809			27AADFF8018N1Z3	1480
60	29AABCV3609C1ZJ	820	Not Available			29AADFG7707M1ZY	1502
61	29AADCH8879C1Z5	825.52	Not Available			29AADCF1911H1ZU	1520
62	29AABCV3609C1ZJ	850	Not Available			06AAECH5372H1ZK	1549
63	29AACCF0683K1ZD	853.47	Not Available			06AAECH5372H1ZK	1549
64	08AAACD4979N1Z0	861	Not Available			29AADCF1911H1ZU	1559
65	29AAACB2894G1ZJ	878.33	Not Available			29AADCF1911H1ZU	1571
66	29AABCV3609C1ZJ	900	Not Available			24AAHCS4768D4Z7	1584
67	29AABCV3609C1ZJ	910	Not Available			29AADCF1911H1ZU	1617
68	29AABCV3609C1ZJ	920	Not Available			29AAJFD5413B1ZR	1624
69	27AABCV3609C1ZN	940	Not Available			29AADCF1911H1ZU	1630
70	29AAACB2894G1ZJ	942.82	Not Available			29AAJFD5413B1ZR	1639
71	29AADCF1911H1ZU	943	943			29AADCF1911H1ZU	1644
72	29AAJFD5413B1ZR	960	960			24AAHCS4768D2Z9	1680
73	33AAGCT1928B1ZR	960.96	Not Available			27AADFF8018N1Z3	1756
74	09AAACV1559Q1ZO	980	Not Available			27AAEFB7230K1ZG	1772
75	24ACNPT0585G1ZB	1043	Not Available			29AADFG7707M1ZY	1779
76	29AAACP0165G1ZL	1044.3	Not Available			29AADCF1911H1ZU	1811
77	27AAEFB7230K1ZG	1050	1050			24AAHCS4768D4Z7	1837
78	29AADFG7707M1ZY	1053	1053			29AADCF1911H1ZU	1841
79	29AABCV3609C1ZJ	1060	Not Available			29AAJFD5413B1ZR	1856
80	29AAACB2894G1ZJ	1060.82	Not Available			06AAECH5372H1ZK	1859
81	29AAACB2894G1ZJ	1060.82	Not Available			24AAHCS4768D4Z7	1862
82	29AAACB2894G1ZJ	1060.82	Not Available			27AADFF8018N1Z3	1907
83	29AADFG7707M1ZY	1074	1074			27ABKFM1457E1Z9	1932
84	29AADFG7707M1ZY	1074	1074			24AAHCS4768D4Z7	1947
85	29AABCV3609C1ZJ	1080	Not Available			29AAJFD5413B1ZR	1972
86	29AAJFD5413B1ZR	1080	1080			29ABGPG5690M1Z2	2006
87	24AABCV3609C1ZT	1100	Not Available			29ABGPG5690M1Z2	2006
88	29AABCV3609C1ZJ	1110	Not Available			29AAJFD5413B1ZR	2030
89	29AABCV3609C1ZJ	1120	Not Available			27AABPA0901H1ZI	2105
90	29AABCV3609C1ZJ	1130	Not Available			24AAHCS4768D4Z7	2171
91	24AAHCS4768D4Z7	1131	1131			27AABPA0901H1ZI	2174
92	08AAACD4979N1Z0	1138.2	Not Available			24ACWFS1439D1ZZ	2185
93	29AABCV3609C1ZJ	1140	Not Available			24AAHCS4768D4Z7	2208
94	06AAECH5372H1ZK	1147	1147			29AADCF1911H1ZU	2209
95	08AAACD4979N1Z0	1157.1	Not Available			27DPXPS7820K1ZT	2213
96	29AABCV3609C1ZJ	1160	Not Available			27DPXPS7820K1ZT	2213
97	29AABCV3609C1ZJ	1170	Not Available			27DPXPS7820K1ZT	2213
98	29AADFG7707M1ZY	1182	1182			27ADOPA2609J1ZL	2231
99	29AADCF1911H1ZU	1192	Not Available			24AAHCS4768D4Z7	2296
100	29AABCV3609C1ZJ	1220	Not Available			29AAJFD5413B1ZR	2320
101	29AABCV3609C1ZJ	1230	Not Available			24AAHCS4768D4Z7	2360
102	29AABCV3609C1ZJ	1230	Not Available			29AAJFD5413B1ZR	2380
103	24AABCV3609C1ZT	1260	Not Available			24AAHCS4768D4Z7	2394
104	27AELPN4350H1ZC	1266	Not Available			27AABPA0901H1ZI	2409
105	29AADCF1911H1ZU	1267	1267			29AADCF1911H1ZU	2421
106	29AABCV3609C1ZJ	1270	Not Available			27AABPA0901H1ZI	2462
107	29AADCF1911H1ZU	1270	1270			24AAHCS4768D4Z7	2494
108	29AABCV3609C1ZJ	1280	Not Available			24AAHCS4768D4Z7	2494
109	29AADFG7707M1ZY	1284	1284			06AAECH5372H1ZK	2514
110	29AABCV3609C1ZJ	1290	Not Available			06AAECH5372H1ZK	2514
111	29AABCV3609C1ZJ	1290	Not Available			27AADFF8018N1Z3	2520
112	27AAJCS7347C1Z7	1322	Not Available			29AADFG7707M1ZY	2600
113	29AABCV3609C1ZJ	1350	Not Available			29AADCF1911H1ZU	2604
114	29AABCV3609C1ZJ	1360	Not Available			29AAJFD5413B1ZR	2618
115	29AADCF1911H1ZU	1401	1401			29AADCF1911H1ZU	2633
116	29AADCF1911H1ZU	1436	Not Available			24AAHCS4768D4Z7	2640
117	29AACFM5833D1ZD	1500	Not Available			29AADCF1911H1ZU	2661
118	29AADFG7707M1ZY	1502	1502			27AADFF8018N1Z3	2661
119	29AADCF1911H1ZU	1520	1520			24AAHCS4768D4Z7	2673
120	29AACCC8157H1ZE	1534	Not Available			29AADCF1911H1ZU	2722
121	29AABCV3609C1ZJ	1550	Not Available			27AADFF8018N1Z3	2740
122	29AADCF1911H1ZU	1559	1559			24AAHCS4768D4Z7	2742
123	29AADCF1911H1ZU	1571	1571			24AAHCS4768D4Z7	2769
124	29AAACP0165G1ZL	1587.39	Not Available			24AAHCS4768D4Z7	2785
125	29AABCV3609C1ZJ	1601	Not Available			29AADFG7707M1ZY	2787
126	29AADCF1911H1ZU	1617	1617			33ABGFM1080P1Z2	2793
127	29AAJFD5413B1ZR	1624	1624			29AADFG7707M1ZY	2811
128	29AADCF1911H1ZU	1630	1630			27AADFF8018N1Z3	2835
129	29AAJFD5413B1ZR	1639	1639			24AAHCS4768D4Z7	2894
130	29AADCF1911H1ZU	1644	1644			29AAJFD5413B1ZR	2900
131	08AAACD4979N1Z0	1669.5	Not Available			29AAJFD5413B1ZR	2900
132	08AAACD4979N1Z0	1670.55	Not Available			29CKEPB2743P1ZU	2912
133	29AADCF1911H1ZU	1696	Not Available			29AADCF1911H1ZU	2930
134	29AABCV3609C1ZJ	1700	Not Available			29AADCF1911H1ZU	2931
135	09AABCV3609C1ZL	1700	Not Available			29AADCF1911H1ZU	2940
136	29AAICA3918J1ZE	1731.77	Not Available			24BGZPS1156F1Z0	2950
137	29AADFG7707M1ZY	1779	1779			29AAJFD5413B1ZR	2975
138	09AABCV3609C1ZL	1828	Not Available			24AAHCS4768D4Z7	2976
139	29AADCF1911H1ZU	1841	1841			29APHPS1047M2ZC	3000
140	29AAJFD5413B1ZR	1856	1856			27AADFF8018N1Z3	3021
141	09AABCV3609C1ZL	1880	Not Available			29AADCF1911H1ZU	3024
142	29AABCV3609C1ZJ	1920	Not Available			29AADFG7707M1ZY	3039
143	24AAHCS4768D4Z7	1947	1947			27AABPA0901H1ZI	3060
144	29AAACS9735K1ZR	1970	Not Available			24AAHCS4768D4Z7	3106
145	29AADCF1911H1ZU	1999	Not Available			27DPXPS7820K1ZT	3186
146	06AALCA0171E1Z3	1999	Not Available			27DPXPS7820K1ZT	3186
Lookup

Cell Formulas
Range	Formula
C2:C146	C2	=IF(COUNTIFS(F$2:F$146,A2,G$2:G$146,B2),B2,"Not Available")

Exactly. Perfect. Thanks Peter. I will have to change the number of rows only and edit and apply the same formula in column H also which I will do. Thanks again

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.