How toVlookup multiple values and match

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
482
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGH
1Particulars AGross Total ANot Matching with BParticulars BGross Total BNot Matching with A
227AAFCA7018N1ZE0#N/A29AAJFD5413B1ZR140.00#N/A
327AAHFH0417M1ZC1.12#N/A29AADFG7707M1ZY199.00199
429AAGCR4375J1ZU76#N/A24AAHCS4768D4Z7236.00236
529AAACD4979N1ZW92.4#N/A29AADCF1911H1ZU288.00288
629AAACD4979N1ZW94.5#N/A29AADCF1911H1ZU355.00355
729AAACD4979N1ZW95.55#N/A24AAHCS4768D4Z7420.00#N/A
807AAACP0165G1ZR118#N/A24AAHCS4768D4Z7443.00#N/A
929AAACD4979N1ZW121.8#N/A24AAHCS4768D4Z7489.00489
1029AAACD4979N1ZW135.45#N/A29AADCF1911H1ZU555.00#N/A
1107AACCI5713M1ZF14914029AADCF1911H1ZU567.00567
1229AAVCS6045D2ZV168.7414029AADCF1911H1ZU578.00578
1329AAACD4979N1ZW178.514029AADCF1911H1ZU586.00586
1429AADFG7707M1ZY19919929AAJFD5413B1ZR588.00588
1529AACFM5833D1ZD20019924AAHCS4768D4Z7591.00591
1629AACFM5833D1ZD20019929AADFG7707M1ZY599.00#N/A
1729AACFM5833D1ZD20019929AADCF1911H1ZU607.00607
1829AACFM5833D1ZD20019929AADFG7707M1ZY608.00#N/A
1929AACFM5833D1ZD20019924AAHCS4768D4Z7609.00#N/A
2029AACFM5833D1ZD20019929AAJFD5413B1ZR696.00696
2127AAQCS4259Q1ZA22019929AAJFD5413B1ZR696.00696
2207AAACP0165G1ZR23623624AAHCS4768D4Z7739.20#N/A
2329AADCF1911H1ZU28828829AADCF1911H1ZU749.00#N/A
2429AADFG7707M1ZY30728829AADCF1911H1ZU756.00756
2529AACFM5833D1ZD33028829AADFG7707M1ZY759.00#N/A
2629AACFM5833D1ZD35028829AADFG7707M1ZY809.00809
2729AACFM5833D1ZD35028829AAJFD5413B1ZR841.00#N/A
2829AADCF1911H1ZU35535524AAHCS4768D4Z7870.00#N/A
2907AAACP0165G1ZR39735524AAHCS4768D4Z7920.00920
3029AACFM5833D1ZD40035506AAECH5372H1ZK929.00#N/A
3129AACFM5833D1ZD40035529AADCF1911H1ZU943.00943
3229AACFM5833D1ZD40035527AABPA0901H1ZI960.00960
3306AABCJ8820B1ZY428.8142029AAJFD5413B1ZR960.00960
3424AAHCS4768D4Z748948929AADFG7707M1ZY1,040.00#N/A
3529AADFG7707M1ZY52848927AAEFB7230K1ZG1,050.001050
3629AADFG7707M1ZY52848924AAHCS4768D4Z71,052.00#N/A
3729AADFG7707M1ZY54348929AADFG7707M1ZY1,053.001053
3829AADCF1911H1ZU56756729AAJFD5413B1ZR1,071.00#N/A
3929AADCF1911H1ZU57857829AAJFD5413B1ZR1,071.00#N/A
4029AADCF1911H1ZU58658629AADFG7707M1ZY1,074.001074
4129AAJFD5413B1ZR58858829AAJFD5413B1ZR1,080.001080
4224AAACP0165G1ZV59058824AAHCS4768D4Z71,100.001100
4327AAACP0165G3ZN59058824AAHCS4768D4Z71,131.001131
4424AAACP0165G1ZV59058806AAECH5372H1ZK1,147.001147
4524AAHCS4768D4Z759159129AADFG7707M1ZY1,182.001182
4629AACFM5833D1ZD60059929AEEPB1993F1ZK1,239.00#N/A
4727AAQCS4259Q1ZA60559929AADFG7707M1ZY1,255.00#N/A
4829AADCF1911H1ZU60760727AABPA0901H1ZI1,260.001260
4929AACFM5833D1ZD65060929AADCF1911H1ZU1,267.001267
5029AAJFD5413B1ZR69669629AADCF1911H1ZU1,270.001270
5107AAACP0165G1ZR70869629AAJFD5413B1ZR1,279.00#N/A
5207AAACP0165G1ZR721.0469629AADFG7707M1ZY1,284.001284
5324AAHCS4768D4Z773969624AAHCS4768D4Z71,329.00#N/A
5429AAMCA7504L1Z8745739.229AAVCS6045D2ZV1,370.00#N/A
5529AADCF1911H1ZU75675629AADCF1911H1ZU1,401.001401
5629AABCV3609C1ZJ76075927ADOPA2609J1ZL1,411.00#N/A
5727AAJCS7347C1Z779375927AABPA0901H1ZI1,428.00#N/A
5829AADFG7707M1ZY80980929AADFG7707M1ZY1,476.00#N/A
5929AADFG7707M1ZY80980927AADFF8018N1Z31,480.00#N/A
6029AABCV3609C1ZJ82080929AADFG7707M1ZY1,502.001502
6129AADCH8879C1Z5825.5280929AADCF1911H1ZU1,520.001520
6229AABCV3609C1ZJ85084106AAECH5372H1ZK1,549.00#N/A
6329AACCF0683K1ZD853.4784106AAECH5372H1ZK1,549.00#N/A
6408AAACD4979N1Z086184129AADCF1911H1ZU1,559.001559
6529AAACB2894G1ZJ878.3387029AADCF1911H1ZU1,571.001571
6629AABCV3609C1ZJ90087024AAHCS4768D4Z71,584.00#N/A
6729AABCV3609C1ZJ91087029AADCF1911H1ZU1,617.001617
6829AABCV3609C1ZJ92092029AAJFD5413B1ZR1,624.001624
6927AABCV3609C1ZN94092929AADCF1911H1ZU1,630.001630
7029AAACB2894G1ZJ942.8292929AAJFD5413B1ZR1,639.001639
7129AADCF1911H1ZU94394329AADCF1911H1ZU1,644.001644
7229AAJFD5413B1ZR96096024AAHCS4768D2Z91,680.00#N/A
7333AAGCT1928B1ZR960.9696027AADFF8018N1Z31,756.00#N/A
7409AAACV1559Q1ZO98096027AAEFB7230K1ZG1,772.00#N/A
7524ACNPT0585G1ZB1043104029AADFG7707M1ZY1,779.001779
7629AAACP0165G1ZL1044.3104029AADCF1911H1ZU1,811.00#N/A
7727AAEFB7230K1ZG1050105024AAHCS4768D4Z71,837.00#N/A
7829AADFG7707M1ZY1053105329AADCF1911H1ZU1,841.001841
7929AABCV3609C1ZJ1060105329AAJFD5413B1ZR1,856.001856
8029AAACB2894G1ZJ1060.82105306AAECH5372H1ZK1,859.00#N/A
8129AAACB2894G1ZJ1060.82105324AAHCS4768D4Z71,862.00#N/A
8229AAACB2894G1ZJ1060.82105327AADFF8018N1Z31,907.00#N/A
8329AADFG7707M1ZY1074107427ABKFM1457E1Z91,932.00#N/A
8429AADFG7707M1ZY1074107424AAHCS4768D4Z71,947.001947
8529AABCV3609C1ZJ1080108029AAJFD5413B1ZR1,972.00#N/A
8629AAJFD5413B1ZR1080108029ABGPG5690M1Z22,006.00#N/A
8724AABCV3609C1ZT1100110029ABGPG5690M1Z22,006.00#N/A
8829AABCV3609C1ZJ1110110029AAJFD5413B1ZR2,030.00#N/A
8929AABCV3609C1ZJ1120110027AABPA0901H1ZI2,105.00#N/A
9029AABCV3609C1ZJ1130110024AAHCS4768D4Z72,171.00#N/A
9124AAHCS4768D4Z71131113127AABPA0901H1ZI2,174.00#N/A
9208AAACD4979N1Z01138.2113124ACWFS1439D1ZZ2,185.00#N/A
9329AABCV3609C1ZJ1140113124AAHCS4768D4Z72,208.00#N/A
9406AAECH5372H1ZK1147114729AADCF1911H1ZU2,209.00#N/A
9508AAACD4979N1Z01157.1114727DPXPS7820K1ZT2,213.00#N/A
9629AABCV3609C1ZJ1160114727DPXPS7820K1ZT2,213.00#N/A
9729AABCV3609C1ZJ1170114727DPXPS7820K1ZT2,213.00#N/A
9829AADFG7707M1ZY1182118227ADOPA2609J1ZL2,231.00#N/A
9929AADCF1911H1ZU1192118224AAHCS4768D4Z72,296.00#N/A
10029AABCV3609C1ZJ1220118229AAJFD5413B1ZR2,320.00#N/A
10129AABCV3609C1ZJ1230118224AAHCS4768D4Z72,360.00#N/A
10229AABCV3609C1ZJ1230118229AAJFD5413B1ZR2,380.00#N/A
10324AABCV3609C1ZT1260126024AAHCS4768D4Z72,394.00#N/A
10427AELPN4350H1ZC1266126027AABPA0901H1ZI2,409.00#N/A
10529AADCF1911H1ZU1267126729AADCF1911H1ZU2,421.00#N/A
10629AABCV3609C1ZJ1270127027AABPA0901H1ZI2,462.00#N/A
10729AADCF1911H1ZU1270127024AAHCS4768D4Z72,494.00#N/A
10829AABCV3609C1ZJ1280127924AAHCS4768D4Z72,494.00#N/A
10929AADFG7707M1ZY1284128406AAECH5372H1ZK2,514.00#N/A
11029AABCV3609C1ZJ1290128406AAECH5372H1ZK2,514.00#N/A
11129AABCV3609C1ZJ1290128427AADFF8018N1Z32,520.00#N/A
11227AAJCS7347C1Z71322128429AADFG7707M1ZY2,600.00#N/A
11329AABCV3609C1ZJ1350132929AADCF1911H1ZU2,604.00#N/A
11429AABCV3609C1ZJ1360132929AAJFD5413B1ZR2,618.00#N/A
11529AADCF1911H1ZU1401140129AADCF1911H1ZU2,633.00#N/A
11629AADCF1911H1ZU1436142824AAHCS4768D4Z72,640.00#N/A
11729AACFM5833D1ZD1500148029AADCF1911H1ZU2,661.00#N/A
11829AADFG7707M1ZY1502150227AADFF8018N1Z32,661.00#N/A
11929AADCF1911H1ZU1520152024AAHCS4768D4Z72,673.00#N/A
12029AACCC8157H1ZE1534152029AADCF1911H1ZU2,722.00#N/A
12129AABCV3609C1ZJ1550154927AADFF8018N1Z32,740.00#N/A
12229AADCF1911H1ZU1559155924AAHCS4768D4Z72,742.00#N/A
12329AADCF1911H1ZU1571157124AAHCS4768D4Z72,769.00#N/A
12429AAACP0165G1ZL1587.39158424AAHCS4768D4Z72,785.00#N/A
12529AABCV3609C1ZJ1601158429AADFG7707M1ZY2,787.00#N/A
12629AADCF1911H1ZU1617161733ABGFM1080P1Z22,793.00#N/A
12729AAJFD5413B1ZR1624162429AADFG7707M1ZY2,811.00#N/A
12829AADCF1911H1ZU1630163027AADFF8018N1Z32,835.00#N/A
12929AAJFD5413B1ZR1639163924AAHCS4768D4Z72,894.00#N/A
13029AADCF1911H1ZU1644164429AAJFD5413B1ZR2,900.00#N/A
13108AAACD4979N1Z01669.5164429AAJFD5413B1ZR2,900.00#N/A
13208AAACD4979N1Z01670.55164429CKEPB2743P1ZU2,912.00#N/A
13329AADCF1911H1ZU1696168029AADCF1911H1ZU2,930.00#N/A
13429AABCV3609C1ZJ1700168029AADCF1911H1ZU2,931.00#N/A
13509AABCV3609C1ZL1700168029AADCF1911H1ZU2,940.00#N/A
13629AAICA3918J1ZE1731.77168024BGZPS1156F1Z02,950.00#N/A
13729AADFG7707M1ZY1779177929AAJFD5413B1ZR2,975.00#N/A
13809AABCV3609C1ZL1828181124AAHCS4768D4Z72,976.00#N/A
13929AADCF1911H1ZU1841184129APHPS1047M2ZC3,000.00#N/A
14029AAJFD5413B1ZR1856185627AADFF8018N1Z33,021.00#N/A
14109AABCV3609C1ZL1880186229AADCF1911H1ZU3,024.00#N/A
14229AABCV3609C1ZJ1920190729AADFG7707M1ZY3,039.00#N/A
14324AAHCS4768D4Z71947194727AABPA0901H1ZI3,060.00#N/A
14429AAACS9735K1ZR1970194724AAHCS4768D4Z73,106.00#N/A
14529AADCF1911H1ZU1999197227DPXPS7820K1ZT3,186.00#N/A
14606AALCA0171E1Z31999197227DPXPS7820K1ZT3,186.00#N/A
Match
Cell Formulas
RangeFormula
C2:C146C2=VLOOKUP(B2,$G$2:$G$146,1)
H2:H146H2=VLOOKUP(G2,$B$2:$B$146,1,0)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,589
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1Particulars AGross Total ANot Matching with BParticulars BGross Total B
227AAFCA7018N1ZE0Not Available29AAJFD5413B1ZR140
327AAHFH0417M1ZC1.12Not Available29AADFG7707M1ZY199
429AAGCR4375J1ZU76Not Available24AAHCS4768D4Z7236
529AAACD4979N1ZW92.4Not Available29AADCF1911H1ZU288
629AAACD4979N1ZW94.5Not Available29AADCF1911H1ZU355
729AAACD4979N1ZW95.55Not Available24AAHCS4768D4Z7420
807AAACP0165G1ZR118Not Available24AAHCS4768D4Z7443
929AAACD4979N1ZW121.8Not Available24AAHCS4768D4Z7489
1029AAACD4979N1ZW135.45Not Available29AADCF1911H1ZU555
1107AACCI5713M1ZF149Not Available29AADCF1911H1ZU567
1229AAVCS6045D2ZV168.74Not Available29AADCF1911H1ZU578
1329AAACD4979N1ZW178.5Not Available29AADCF1911H1ZU586
1429AADFG7707M1ZY19919929AAJFD5413B1ZR588
1529AACFM5833D1ZD200Not Available24AAHCS4768D4Z7591
1629AACFM5833D1ZD200Not Available29AADFG7707M1ZY599
1729AACFM5833D1ZD200Not Available29AADCF1911H1ZU607
1829AACFM5833D1ZD200Not Available29AADFG7707M1ZY608
1929AACFM5833D1ZD200Not Available24AAHCS4768D4Z7609
2029AACFM5833D1ZD200Not Available29AAJFD5413B1ZR696
2127AAQCS4259Q1ZA220Not Available29AAJFD5413B1ZR696
2207AAACP0165G1ZR236Not Available24AAHCS4768D4Z7739.2
2329AADCF1911H1ZU28828829AADCF1911H1ZU749
2429AADFG7707M1ZY307Not Available29AADCF1911H1ZU756
2529AACFM5833D1ZD330Not Available29AADFG7707M1ZY759
2629AACFM5833D1ZD350Not Available29AADFG7707M1ZY809
2729AACFM5833D1ZD350Not Available29AAJFD5413B1ZR841
2829AADCF1911H1ZU35535524AAHCS4768D4Z7870
2907AAACP0165G1ZR397Not Available24AAHCS4768D4Z7920
3029AACFM5833D1ZD400Not Available06AAECH5372H1ZK929
3129AACFM5833D1ZD400Not Available29AADCF1911H1ZU943
3229AACFM5833D1ZD400Not Available27AABPA0901H1ZI960
3306AABCJ8820B1ZY428.81Not Available29AAJFD5413B1ZR960
3424AAHCS4768D4Z748948929AADFG7707M1ZY1040
3529AADFG7707M1ZY528Not Available27AAEFB7230K1ZG1050
3629AADFG7707M1ZY528Not Available24AAHCS4768D4Z71052
3729AADFG7707M1ZY543Not Available29AADFG7707M1ZY1053
3829AADCF1911H1ZU56756729AAJFD5413B1ZR1071
3929AADCF1911H1ZU57857829AAJFD5413B1ZR1071
4029AADCF1911H1ZU58658629AADFG7707M1ZY1074
4129AAJFD5413B1ZR58858829AAJFD5413B1ZR1080
4224AAACP0165G1ZV590Not Available24AAHCS4768D4Z71100
4327AAACP0165G3ZN590Not Available24AAHCS4768D4Z71131
4424AAACP0165G1ZV590Not Available06AAECH5372H1ZK1147
4524AAHCS4768D4Z759159129AADFG7707M1ZY1182
4629AACFM5833D1ZD600Not Available29AEEPB1993F1ZK1239
4727AAQCS4259Q1ZA605Not Available29AADFG7707M1ZY1255
4829AADCF1911H1ZU60760727AABPA0901H1ZI1260
4929AACFM5833D1ZD650Not Available29AADCF1911H1ZU1267
5029AAJFD5413B1ZR69669629AADCF1911H1ZU1270
5107AAACP0165G1ZR708Not Available29AAJFD5413B1ZR1279
5207AAACP0165G1ZR721.04Not Available29AADFG7707M1ZY1284
5324AAHCS4768D4Z7739Not Available24AAHCS4768D4Z71329
5429AAMCA7504L1Z8745Not Available29AAVCS6045D2ZV1370
5529AADCF1911H1ZU75675629AADCF1911H1ZU1401
5629AABCV3609C1ZJ760Not Available27ADOPA2609J1ZL1411
5727AAJCS7347C1Z7793Not Available27AABPA0901H1ZI1428
5829AADFG7707M1ZY80980929AADFG7707M1ZY1476
5929AADFG7707M1ZY80980927AADFF8018N1Z31480
6029AABCV3609C1ZJ820Not Available29AADFG7707M1ZY1502
6129AADCH8879C1Z5825.52Not Available29AADCF1911H1ZU1520
6229AABCV3609C1ZJ850Not Available06AAECH5372H1ZK1549
6329AACCF0683K1ZD853.47Not Available06AAECH5372H1ZK1549
6408AAACD4979N1Z0861Not Available29AADCF1911H1ZU1559
6529AAACB2894G1ZJ878.33Not Available29AADCF1911H1ZU1571
6629AABCV3609C1ZJ900Not Available24AAHCS4768D4Z71584
6729AABCV3609C1ZJ910Not Available29AADCF1911H1ZU1617
6829AABCV3609C1ZJ920Not Available29AAJFD5413B1ZR1624
6927AABCV3609C1ZN940Not Available29AADCF1911H1ZU1630
7029AAACB2894G1ZJ942.82Not Available29AAJFD5413B1ZR1639
7129AADCF1911H1ZU94394329AADCF1911H1ZU1644
7229AAJFD5413B1ZR96096024AAHCS4768D2Z91680
7333AAGCT1928B1ZR960.96Not Available27AADFF8018N1Z31756
7409AAACV1559Q1ZO980Not Available27AAEFB7230K1ZG1772
7524ACNPT0585G1ZB1043Not Available29AADFG7707M1ZY1779
7629AAACP0165G1ZL1044.3Not Available29AADCF1911H1ZU1811
7727AAEFB7230K1ZG1050105024AAHCS4768D4Z71837
7829AADFG7707M1ZY1053105329AADCF1911H1ZU1841
7929AABCV3609C1ZJ1060Not Available29AAJFD5413B1ZR1856
8029AAACB2894G1ZJ1060.82Not Available06AAECH5372H1ZK1859
8129AAACB2894G1ZJ1060.82Not Available24AAHCS4768D4Z71862
8229AAACB2894G1ZJ1060.82Not Available27AADFF8018N1Z31907
8329AADFG7707M1ZY1074107427ABKFM1457E1Z91932
8429AADFG7707M1ZY1074107424AAHCS4768D4Z71947
8529AABCV3609C1ZJ1080Not Available29AAJFD5413B1ZR1972
8629AAJFD5413B1ZR1080108029ABGPG5690M1Z22006
8724AABCV3609C1ZT1100Not Available29ABGPG5690M1Z22006
8829AABCV3609C1ZJ1110Not Available29AAJFD5413B1ZR2030
8929AABCV3609C1ZJ1120Not Available27AABPA0901H1ZI2105
9029AABCV3609C1ZJ1130Not Available24AAHCS4768D4Z72171
9124AAHCS4768D4Z71131113127AABPA0901H1ZI2174
9208AAACD4979N1Z01138.2Not Available24ACWFS1439D1ZZ2185
9329AABCV3609C1ZJ1140Not Available24AAHCS4768D4Z72208
9406AAECH5372H1ZK1147114729AADCF1911H1ZU2209
9508AAACD4979N1Z01157.1Not Available27DPXPS7820K1ZT2213
9629AABCV3609C1ZJ1160Not Available27DPXPS7820K1ZT2213
9729AABCV3609C1ZJ1170Not Available27DPXPS7820K1ZT2213
9829AADFG7707M1ZY1182118227ADOPA2609J1ZL2231
9929AADCF1911H1ZU1192Not Available24AAHCS4768D4Z72296
10029AABCV3609C1ZJ1220Not Available29AAJFD5413B1ZR2320
10129AABCV3609C1ZJ1230Not Available24AAHCS4768D4Z72360
10229AABCV3609C1ZJ1230Not Available29AAJFD5413B1ZR2380
10324AABCV3609C1ZT1260Not Available24AAHCS4768D4Z72394
10427AELPN4350H1ZC1266Not Available27AABPA0901H1ZI2409
10529AADCF1911H1ZU1267126729AADCF1911H1ZU2421
10629AABCV3609C1ZJ1270Not Available27AABPA0901H1ZI2462
10729AADCF1911H1ZU1270127024AAHCS4768D4Z72494
10829AABCV3609C1ZJ1280Not Available24AAHCS4768D4Z72494
10929AADFG7707M1ZY1284128406AAECH5372H1ZK2514
11029AABCV3609C1ZJ1290Not Available06AAECH5372H1ZK2514
11129AABCV3609C1ZJ1290Not Available27AADFF8018N1Z32520
11227AAJCS7347C1Z71322Not Available29AADFG7707M1ZY2600
11329AABCV3609C1ZJ1350Not Available29AADCF1911H1ZU2604
11429AABCV3609C1ZJ1360Not Available29AAJFD5413B1ZR2618
11529AADCF1911H1ZU1401140129AADCF1911H1ZU2633
11629AADCF1911H1ZU1436Not Available24AAHCS4768D4Z72640
11729AACFM5833D1ZD1500Not Available29AADCF1911H1ZU2661
11829AADFG7707M1ZY1502150227AADFF8018N1Z32661
11929AADCF1911H1ZU1520152024AAHCS4768D4Z72673
12029AACCC8157H1ZE1534Not Available29AADCF1911H1ZU2722
12129AABCV3609C1ZJ1550Not Available27AADFF8018N1Z32740
12229AADCF1911H1ZU1559155924AAHCS4768D4Z72742
12329AADCF1911H1ZU1571157124AAHCS4768D4Z72769
12429AAACP0165G1ZL1587.39Not Available24AAHCS4768D4Z72785
12529AABCV3609C1ZJ1601Not Available29AADFG7707M1ZY2787
12629AADCF1911H1ZU1617161733ABGFM1080P1Z22793
12729AAJFD5413B1ZR1624162429AADFG7707M1ZY2811
12829AADCF1911H1ZU1630163027AADFF8018N1Z32835
12929AAJFD5413B1ZR1639163924AAHCS4768D4Z72894
13029AADCF1911H1ZU1644164429AAJFD5413B1ZR2900
13108AAACD4979N1Z01669.5Not Available29AAJFD5413B1ZR2900
13208AAACD4979N1Z01670.55Not Available29CKEPB2743P1ZU2912
13329AADCF1911H1ZU1696Not Available29AADCF1911H1ZU2930
13429AABCV3609C1ZJ1700Not Available29AADCF1911H1ZU2931
13509AABCV3609C1ZL1700Not Available29AADCF1911H1ZU2940
13629AAICA3918J1ZE1731.77Not Available24BGZPS1156F1Z02950
13729AADFG7707M1ZY1779177929AAJFD5413B1ZR2975
13809AABCV3609C1ZL1828Not Available24AAHCS4768D4Z72976
13929AADCF1911H1ZU1841184129APHPS1047M2ZC3000
14029AAJFD5413B1ZR1856185627AADFF8018N1Z33021
14109AABCV3609C1ZL1880Not Available29AADCF1911H1ZU3024
14229AABCV3609C1ZJ1920Not Available29AADFG7707M1ZY3039
14324AAHCS4768D4Z71947194727AABPA0901H1ZI3060
14429AAACS9735K1ZR1970Not Available24AAHCS4768D4Z73106
14529AADCF1911H1ZU1999Not Available27DPXPS7820K1ZT3186
14606AALCA0171E1Z31999Not Available27DPXPS7820K1ZT3186
Lookup
Cell Formulas
RangeFormula
C2:C146C2=IF(COUNTIFS(F$2:F$146,A2,G$2:G$146,B2),B2,"Not Available")
 
Solution

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
482
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFG
1Particulars AGross Total ANot Matching with BParticulars BGross Total B
227AAFCA7018N1ZE0Not Available29AAJFD5413B1ZR140
327AAHFH0417M1ZC1.12Not Available29AADFG7707M1ZY199
429AAGCR4375J1ZU76Not Available24AAHCS4768D4Z7236
529AAACD4979N1ZW92.4Not Available29AADCF1911H1ZU288
629AAACD4979N1ZW94.5Not Available29AADCF1911H1ZU355
729AAACD4979N1ZW95.55Not Available24AAHCS4768D4Z7420
807AAACP0165G1ZR118Not Available24AAHCS4768D4Z7443
929AAACD4979N1ZW121.8Not Available24AAHCS4768D4Z7489
1029AAACD4979N1ZW135.45Not Available29AADCF1911H1ZU555
1107AACCI5713M1ZF149Not Available29AADCF1911H1ZU567
1229AAVCS6045D2ZV168.74Not Available29AADCF1911H1ZU578
1329AAACD4979N1ZW178.5Not Available29AADCF1911H1ZU586
1429AADFG7707M1ZY19919929AAJFD5413B1ZR588
1529AACFM5833D1ZD200Not Available24AAHCS4768D4Z7591
1629AACFM5833D1ZD200Not Available29AADFG7707M1ZY599
1729AACFM5833D1ZD200Not Available29AADCF1911H1ZU607
1829AACFM5833D1ZD200Not Available29AADFG7707M1ZY608
1929AACFM5833D1ZD200Not Available24AAHCS4768D4Z7609
2029AACFM5833D1ZD200Not Available29AAJFD5413B1ZR696
2127AAQCS4259Q1ZA220Not Available29AAJFD5413B1ZR696
2207AAACP0165G1ZR236Not Available24AAHCS4768D4Z7739.2
2329AADCF1911H1ZU28828829AADCF1911H1ZU749
2429AADFG7707M1ZY307Not Available29AADCF1911H1ZU756
2529AACFM5833D1ZD330Not Available29AADFG7707M1ZY759
2629AACFM5833D1ZD350Not Available29AADFG7707M1ZY809
2729AACFM5833D1ZD350Not Available29AAJFD5413B1ZR841
2829AADCF1911H1ZU35535524AAHCS4768D4Z7870
2907AAACP0165G1ZR397Not Available24AAHCS4768D4Z7920
3029AACFM5833D1ZD400Not Available06AAECH5372H1ZK929
3129AACFM5833D1ZD400Not Available29AADCF1911H1ZU943
3229AACFM5833D1ZD400Not Available27AABPA0901H1ZI960
3306AABCJ8820B1ZY428.81Not Available29AAJFD5413B1ZR960
3424AAHCS4768D4Z748948929AADFG7707M1ZY1040
3529AADFG7707M1ZY528Not Available27AAEFB7230K1ZG1050
3629AADFG7707M1ZY528Not Available24AAHCS4768D4Z71052
3729AADFG7707M1ZY543Not Available29AADFG7707M1ZY1053
3829AADCF1911H1ZU56756729AAJFD5413B1ZR1071
3929AADCF1911H1ZU57857829AAJFD5413B1ZR1071
4029AADCF1911H1ZU58658629AADFG7707M1ZY1074
4129AAJFD5413B1ZR58858829AAJFD5413B1ZR1080
4224AAACP0165G1ZV590Not Available24AAHCS4768D4Z71100
4327AAACP0165G3ZN590Not Available24AAHCS4768D4Z71131
4424AAACP0165G1ZV590Not Available06AAECH5372H1ZK1147
4524AAHCS4768D4Z759159129AADFG7707M1ZY1182
4629AACFM5833D1ZD600Not Available29AEEPB1993F1ZK1239
4727AAQCS4259Q1ZA605Not Available29AADFG7707M1ZY1255
4829AADCF1911H1ZU60760727AABPA0901H1ZI1260
4929AACFM5833D1ZD650Not Available29AADCF1911H1ZU1267
5029AAJFD5413B1ZR69669629AADCF1911H1ZU1270
5107AAACP0165G1ZR708Not Available29AAJFD5413B1ZR1279
5207AAACP0165G1ZR721.04Not Available29AADFG7707M1ZY1284
5324AAHCS4768D4Z7739Not Available24AAHCS4768D4Z71329
5429AAMCA7504L1Z8745Not Available29AAVCS6045D2ZV1370
5529AADCF1911H1ZU75675629AADCF1911H1ZU1401
5629AABCV3609C1ZJ760Not Available27ADOPA2609J1ZL1411
5727AAJCS7347C1Z7793Not Available27AABPA0901H1ZI1428
5829AADFG7707M1ZY80980929AADFG7707M1ZY1476
5929AADFG7707M1ZY80980927AADFF8018N1Z31480
6029AABCV3609C1ZJ820Not Available29AADFG7707M1ZY1502
6129AADCH8879C1Z5825.52Not Available29AADCF1911H1ZU1520
6229AABCV3609C1ZJ850Not Available06AAECH5372H1ZK1549
6329AACCF0683K1ZD853.47Not Available06AAECH5372H1ZK1549
6408AAACD4979N1Z0861Not Available29AADCF1911H1ZU1559
6529AAACB2894G1ZJ878.33Not Available29AADCF1911H1ZU1571
6629AABCV3609C1ZJ900Not Available24AAHCS4768D4Z71584
6729AABCV3609C1ZJ910Not Available29AADCF1911H1ZU1617
6829AABCV3609C1ZJ920Not Available29AAJFD5413B1ZR1624
6927AABCV3609C1ZN940Not Available29AADCF1911H1ZU1630
7029AAACB2894G1ZJ942.82Not Available29AAJFD5413B1ZR1639
7129AADCF1911H1ZU94394329AADCF1911H1ZU1644
7229AAJFD5413B1ZR96096024AAHCS4768D2Z91680
7333AAGCT1928B1ZR960.96Not Available27AADFF8018N1Z31756
7409AAACV1559Q1ZO980Not Available27AAEFB7230K1ZG1772
7524ACNPT0585G1ZB1043Not Available29AADFG7707M1ZY1779
7629AAACP0165G1ZL1044.3Not Available29AADCF1911H1ZU1811
7727AAEFB7230K1ZG1050105024AAHCS4768D4Z71837
7829AADFG7707M1ZY1053105329AADCF1911H1ZU1841
7929AABCV3609C1ZJ1060Not Available29AAJFD5413B1ZR1856
8029AAACB2894G1ZJ1060.82Not Available06AAECH5372H1ZK1859
8129AAACB2894G1ZJ1060.82Not Available24AAHCS4768D4Z71862
8229AAACB2894G1ZJ1060.82Not Available27AADFF8018N1Z31907
8329AADFG7707M1ZY1074107427ABKFM1457E1Z91932
8429AADFG7707M1ZY1074107424AAHCS4768D4Z71947
8529AABCV3609C1ZJ1080Not Available29AAJFD5413B1ZR1972
8629AAJFD5413B1ZR1080108029ABGPG5690M1Z22006
8724AABCV3609C1ZT1100Not Available29ABGPG5690M1Z22006
8829AABCV3609C1ZJ1110Not Available29AAJFD5413B1ZR2030
8929AABCV3609C1ZJ1120Not Available27AABPA0901H1ZI2105
9029AABCV3609C1ZJ1130Not Available24AAHCS4768D4Z72171
9124AAHCS4768D4Z71131113127AABPA0901H1ZI2174
9208AAACD4979N1Z01138.2Not Available24ACWFS1439D1ZZ2185
9329AABCV3609C1ZJ1140Not Available24AAHCS4768D4Z72208
9406AAECH5372H1ZK1147114729AADCF1911H1ZU2209
9508AAACD4979N1Z01157.1Not Available27DPXPS7820K1ZT2213
9629AABCV3609C1ZJ1160Not Available27DPXPS7820K1ZT2213
9729AABCV3609C1ZJ1170Not Available27DPXPS7820K1ZT2213
9829AADFG7707M1ZY1182118227ADOPA2609J1ZL2231
9929AADCF1911H1ZU1192Not Available24AAHCS4768D4Z72296
10029AABCV3609C1ZJ1220Not Available29AAJFD5413B1ZR2320
10129AABCV3609C1ZJ1230Not Available24AAHCS4768D4Z72360
10229AABCV3609C1ZJ1230Not Available29AAJFD5413B1ZR2380
10324AABCV3609C1ZT1260Not Available24AAHCS4768D4Z72394
10427AELPN4350H1ZC1266Not Available27AABPA0901H1ZI2409
10529AADCF1911H1ZU1267126729AADCF1911H1ZU2421
10629AABCV3609C1ZJ1270Not Available27AABPA0901H1ZI2462
10729AADCF1911H1ZU1270127024AAHCS4768D4Z72494
10829AABCV3609C1ZJ1280Not Available24AAHCS4768D4Z72494
10929AADFG7707M1ZY1284128406AAECH5372H1ZK2514
11029AABCV3609C1ZJ1290Not Available06AAECH5372H1ZK2514
11129AABCV3609C1ZJ1290Not Available27AADFF8018N1Z32520
11227AAJCS7347C1Z71322Not Available29AADFG7707M1ZY2600
11329AABCV3609C1ZJ1350Not Available29AADCF1911H1ZU2604
11429AABCV3609C1ZJ1360Not Available29AAJFD5413B1ZR2618
11529AADCF1911H1ZU1401140129AADCF1911H1ZU2633
11629AADCF1911H1ZU1436Not Available24AAHCS4768D4Z72640
11729AACFM5833D1ZD1500Not Available29AADCF1911H1ZU2661
11829AADFG7707M1ZY1502150227AADFF8018N1Z32661
11929AADCF1911H1ZU1520152024AAHCS4768D4Z72673
12029AACCC8157H1ZE1534Not Available29AADCF1911H1ZU2722
12129AABCV3609C1ZJ1550Not Available27AADFF8018N1Z32740
12229AADCF1911H1ZU1559155924AAHCS4768D4Z72742
12329AADCF1911H1ZU1571157124AAHCS4768D4Z72769
12429AAACP0165G1ZL1587.39Not Available24AAHCS4768D4Z72785
12529AABCV3609C1ZJ1601Not Available29AADFG7707M1ZY2787
12629AADCF1911H1ZU1617161733ABGFM1080P1Z22793
12729AAJFD5413B1ZR1624162429AADFG7707M1ZY2811
12829AADCF1911H1ZU1630163027AADFF8018N1Z32835
12929AAJFD5413B1ZR1639163924AAHCS4768D4Z72894
13029AADCF1911H1ZU1644164429AAJFD5413B1ZR2900
13108AAACD4979N1Z01669.5Not Available29AAJFD5413B1ZR2900
13208AAACD4979N1Z01670.55Not Available29CKEPB2743P1ZU2912
13329AADCF1911H1ZU1696Not Available29AADCF1911H1ZU2930
13429AABCV3609C1ZJ1700Not Available29AADCF1911H1ZU2931
13509AABCV3609C1ZL1700Not Available29AADCF1911H1ZU2940
13629AAICA3918J1ZE1731.77Not Available24BGZPS1156F1Z02950
13729AADFG7707M1ZY1779177929AAJFD5413B1ZR2975
13809AABCV3609C1ZL1828Not Available24AAHCS4768D4Z72976
13929AADCF1911H1ZU1841184129APHPS1047M2ZC3000
14029AAJFD5413B1ZR1856185627AADFF8018N1Z33021
14109AABCV3609C1ZL1880Not Available29AADCF1911H1ZU3024
14229AABCV3609C1ZJ1920Not Available29AADFG7707M1ZY3039
14324AAHCS4768D4Z71947194727AABPA0901H1ZI3060
14429AAACS9735K1ZR1970Not Available24AAHCS4768D4Z73106
14529AADCF1911H1ZU1999Not Available27DPXPS7820K1ZT3186
14606AALCA0171E1Z31999Not Available27DPXPS7820K1ZT3186
Lookup
Cell Formulas
RangeFormula
C2:C146C2=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,589
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,323
Messages
5,641,530
Members
417,215
Latest member
Diaryman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top