RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- 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.
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) |