Index Match Function within selected Blank Cells

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello experts

As the invoice number in TALLY are not correct, I have to enter and the matching invoice number in TALLY rows with the PORTAL invoice number in column E. The only common thing between them is the GSTIN number and TRADE NAME. The date of tally is equal to PORTAL invoice date and greater than portal in most of the cases. Even the Invoice value in tally may be equal or less or more than the PORTAL Invoice value with a nominal difference of less than +1 or -1. I have to select the blank cells in column E and insert maybe a index match function which I am not so good at. If anyone can help me to write the formula, I would really appreciate it.


practice 2A match 23082021.xlsx
ABCDEFGH
1LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)
2706PORTAL24BGZPS0000F1Z0January141404-09-20202950.00
31807TALLY24BGZPS0000F1Z0January 46404-09-20202950.00
4832PORTAL07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
51535TALLY07EUDPS1111F1ZFFebruary 275802-07-202010500.00
61120PORTAL07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
72074TALLY07EUDPS1111F1ZFFebruary 303226-10-202010500.00
81073PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819-01-20218118.60
92507TALLY27AHTPJ2222N1ZNMarch Jan-1498 19-01-20218119.00
101678TALLY27AHTPJ2222N1ZNMarch 33507-08-20208819.00
11503PORTAL27AHTPJ2222N1ZNMarchS-226S-22610-08-202015417.00
12507PORTAL27AHTPJ2222N1ZNMarchS-318S-31826-08-202015745.00
131762TALLY27AHTPJ2222N1ZNMarch 41926-08-202015745.00
14506PORTAL27AHTPJ2222N1ZNMarchS-303S-30324-08-202019727.00
151747TALLY27AHTPJ2222N1ZNMarch 40424-08-202019727.00
161670TALLY27AHTPJ2222N1ZNMarch 32706-08-202019733.00
17501PORTAL27AHTPJ2222N1ZNMarchS-206S-20606-08-202019744.00
18900PORTAL27AHTPJ2222N1ZNMarchS-736S-73621-10-202020300.00
192057TALLY27AHTPJ2222N1ZNMarch 73621-10-202020300.00
201289PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522-02-202120821.50
212649TALLY27AHTPJ2222N1ZNMarch Feb-1785 22-02-202120822.00
222515TALLY27AHTPJ2222N1ZNMarch Jan-1505 20-01-202121268.00
231074PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520-01-202121268.28
24759PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230-11-202021275.00
252227TALLY27AHTPJ2222N1ZNMarch Dec-1102 01-12-202021275.00
262318TALLY27AHTPJ2222N1ZNMarch Dec-1235 15-12-202021322.00
27586PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113-09-202032422.00
281854TALLY29AAJFD3333B1ZRApril 51113-09-202032422.00
291460PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120-03-202134550.00
302728TALLY29AAJFD3333B1ZRApril 974820-03-202134550.00
31583PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103-09-202037845.00
321796TALLY29AAJFD3333B1ZRApril 45303-09-202037845.00
331518PORTAL06ATLPK4444D1ZKMay1938193822-03-202112222.00
341519PORTAL06ATLPK4444D1ZKMay1939193922-03-202112222.00
352733TALLY06ATLPK4444D1ZKMay 193922-03-202112222.00
362734TALLY06ATLPK4444D1ZKMay 193822-03-202112222.00
37527PORTAL06ATLPK4444D1ZKMay58258219-09-202026775.00
381894TALLY06ATLPK4444D1ZKMay 55119-09-202026775.00
391896TALLY06ATLPK4444D1ZKMay 55319-09-202026775.00
402433TALLY06ATLPK4444D1ZKMay Jan-1594 05-01-202126880.00
411062PORTAL06ATLPK4444D1ZKMay1594159408-01-202126880.00
42809PORTAL06ATLPK4444D1ZKMay1302130217-12-202027300.00
432338TALLY06ATLPK4444D1ZKMay Dec-1302 17-12-202027300.00
441065PORTAL06ATLPK4444D1ZKMay1642164212-01-202166150.00
451066PORTAL06ATLPK4444D1ZKMay1643164312-01-202166150.00
461067PORTAL06ATLPK4444D1ZKMay1644164412-01-202166150.00
472470TALLY06ATLPK4444D1ZKMay Jan-1642 12-01-202166150.00
482471TALLY06ATLPK4444D1ZKMay Jan-1643 12-01-202166150.00
492472TALLY06ATLPK4444D1ZKMay Jan-1644 12-01-202166150.00
501563TALLY29AADCF5555H1ZUJune 15707-07-2020355.00
5116PORTAL29AADCF5555H1ZUJune15715708-07-2020355.00
52800PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928-11-202021000.00
532229TALLY29AADCF5555H1ZUJuly Dec-529 01-12-202021000.00
542608TALLY29AADCF5555H1ZUJuly Feb-641 09-02-202122722.00
551430PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110-02-202122722.00
561110PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123-12-202025284.00
572370TALLY29AADCF5555H1ZUJuly Dec-571 23-12-202025284.00
581528TALLY33AAACY6666M1Z2August 115501-07-20202950.00
59417PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307-07-20202950.00
seperate error rows
Cell Formulas
RangeFormula
E2:E59E2=IF(B2="portal",F2,"")
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
If none of the conditions match then the tally row should show the same invoice number in column E as in F
 
Upvote 0
Could you tell us the desired (expected) results in some rows and the logic to get them?

M.
 
Upvote 0
If the Tin number, trade name and amount are a match with both tally and portal, then I should get the invoice number of portal in the blank cells. Else I should get the invoice number what is in tally. This image contains the corrected invoice numbers but done manually. As the data is more than 10,000 rows, if possible an index match or if function would save a considerable lot of time.
Marcelo Branco said:
Could you tell us the desired (expected) results in some rows and the logic to get them?

M.
Click to expand...
practice 2A match 23082021.xlsx
ABCDEFGH
1LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)
2706PORTAL24BGZPS0000F1Z0January141404-09-20202950.00
31807TALLY24BGZPS0000F1Z0January1446404-09-20202950.00
4832PORTAL07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
51535TALLY07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
61120PORTAL07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
72074TALLY07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
81073PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819-01-20218118.60
92507TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19-01-20218119.00
101678TALLY27AHTPJ2222N1ZNMarch33533507-08-20208819.00
11503PORTAL27AHTPJ2222N1ZNMarchS-226S-22610-08-202015417.00
12507PORTAL27AHTPJ2222N1ZNMarchS-318S-31826-08-202015745.00
131762TALLY27AHTPJ2222N1ZNMarchS-31841926-08-202015745.00
14506PORTAL27AHTPJ2222N1ZNMarchS-303S-30324-08-202019727.00
151747TALLY27AHTPJ2222N1ZNMarchS-30340424-08-202019727.00
161670TALLY27AHTPJ2222N1ZNMarchS-20632706-08-202019733.00
17501PORTAL27AHTPJ2222N1ZNMarchS-206S-20606-08-202019744.00
18900PORTAL27AHTPJ2222N1ZNMarchS-736S-73621-10-202020300.00
192057TALLY27AHTPJ2222N1ZNMarchS-73673621-10-202020300.00
201289PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522-02-202120821.50
212649TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22-02-202120822.00
222515TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505 20-01-202121268.00
231074PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520-01-202121268.28
24759PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230-11-202021275.00
252227TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01-12-202021275.00
262318TALLY27AHTPJ2222N1ZNMarchDec-1235 Dec-1235 15-12-202021322.00
27586PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113-09-202032422.00
281854TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113-09-202032422.00
291460PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120-03-202134550.00
302728TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820-03-202134550.00
31583PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103-09-202037845.00
321796TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303-09-202037845.00
331518PORTAL06ATLPK4444D1ZKMay1938193822-03-202112222.00
341519PORTAL06ATLPK4444D1ZKMay1939193922-03-202112222.00
352733TALLY06ATLPK4444D1ZKMay1938193922-03-202112222.00
362734TALLY06ATLPK4444D1ZKMay1939193822-03-202112222.00
37527PORTAL06ATLPK4444D1ZKMay58258219-09-202026775.00
381894TALLY06ATLPK4444D1ZKMay58255119-09-202026775.00
391896TALLY06ATLPK4444D1ZKMay55355319-09-202026775.00
402433TALLY06ATLPK4444D1ZKMay1594Jan-1594 05-01-202126880.00
411062PORTAL06ATLPK4444D1ZKMay1594159408-01-202126880.00
42809PORTAL06ATLPK4444D1ZKMay1302130217-12-202027300.00
432338TALLY06ATLPK4444D1ZKMay1302Dec-1302 17-12-202027300.00
441065PORTAL06ATLPK4444D1ZKMay1642164212-01-202166150.00
451066PORTAL06ATLPK4444D1ZKMay1643164312-01-202166150.00
461067PORTAL06ATLPK4444D1ZKMay1644164412-01-202166150.00
472470TALLY06ATLPK4444D1ZKMay1642Jan-1642 12-01-202166150.00
482471TALLY06ATLPK4444D1ZKMay1643Jan-1643 12-01-202166150.00
492472TALLY06ATLPK4444D1ZKMay1644Jan-1644 12-01-202166150.00
501563TALLY29AADCF5555H1ZUJune15715707-07-2020355.00
5116PORTAL29AADCF5555H1ZUJune15715708-07-2020355.00
52800PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928-11-202021000.00
532229TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01-12-202021000.00
542608TALLY29AADCF5555H1ZUJulyRI/20-21/641Feb-641 09-02-202122722.00
551430PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110-02-202122722.00
561110PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123-12-202025284.00
572370TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23-12-202025284.00
581528TALLY33AAACY6666M1Z2AugustCBE0123115501-07-20202950.00
59417PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307-07-20202950.00
seperate error rows
Cell Formulas
RangeFormula
E10,E39,E26E10=F10
 
Upvote 0
RAJESH1960 said:
If the Tin number, trade name and amount are a match with both tally and portal, then I should get the invoice number of portal in the blank cells. Else I should get the invoice number what is in tally. This image contains the corrected invoice numbers but done manually. As the data is more than 10,000 rows, if possible an index match or if function would save a considerable lot of time.

practice 2A match 23082021.xlsx
ABCDEFGH
1LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)
2706PORTAL24BGZPS0000F1Z0January141404-09-20202950.00
31807TALLY24BGZPS0000F1Z0January1446404-09-20202950.00
4832PORTAL07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
51535TALLY07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
61120PORTAL07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
72074TALLY07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
81073PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819-01-20218118.60
92507TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19-01-20218119.00
101678TALLY27AHTPJ2222N1ZNMarch33533507-08-20208819.00
11503PORTAL27AHTPJ2222N1ZNMarchS-226S-22610-08-202015417.00
12507PORTAL27AHTPJ2222N1ZNMarchS-318S-31826-08-202015745.00
131762TALLY27AHTPJ2222N1ZNMarchS-31841926-08-202015745.00
14506PORTAL27AHTPJ2222N1ZNMarchS-303S-30324-08-202019727.00
151747TALLY27AHTPJ2222N1ZNMarchS-30340424-08-202019727.00
161670TALLY27AHTPJ2222N1ZNMarchS-20632706-08-202019733.00
17501PORTAL27AHTPJ2222N1ZNMarchS-206S-20606-08-202019744.00
18900PORTAL27AHTPJ2222N1ZNMarchS-736S-73621-10-202020300.00
192057TALLY27AHTPJ2222N1ZNMarchS-73673621-10-202020300.00
201289PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522-02-202120821.50
212649TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22-02-202120822.00
222515TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505 20-01-202121268.00
231074PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520-01-202121268.28
24759PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230-11-202021275.00
252227TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01-12-202021275.00
262318TALLY27AHTPJ2222N1ZNMarchDec-1235 Dec-1235 15-12-202021322.00
27586PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113-09-202032422.00
281854TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113-09-202032422.00
291460PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120-03-202134550.00
302728TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820-03-202134550.00
31583PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103-09-202037845.00
321796TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303-09-202037845.00
331518PORTAL06ATLPK4444D1ZKMay1938193822-03-202112222.00
341519PORTAL06ATLPK4444D1ZKMay1939193922-03-202112222.00
352733TALLY06ATLPK4444D1ZKMay1938193922-03-202112222.00
362734TALLY06ATLPK4444D1ZKMay1939193822-03-202112222.00
37527PORTAL06ATLPK4444D1ZKMay58258219-09-202026775.00
381894TALLY06ATLPK4444D1ZKMay58255119-09-202026775.00
391896TALLY06ATLPK4444D1ZKMay55355319-09-202026775.00
402433TALLY06ATLPK4444D1ZKMay1594Jan-1594 05-01-202126880.00
411062PORTAL06ATLPK4444D1ZKMay1594159408-01-202126880.00
42809PORTAL06ATLPK4444D1ZKMay1302130217-12-202027300.00
432338TALLY06ATLPK4444D1ZKMay1302Dec-1302 17-12-202027300.00
441065PORTAL06ATLPK4444D1ZKMay1642164212-01-202166150.00
451066PORTAL06ATLPK4444D1ZKMay1643164312-01-202166150.00
461067PORTAL06ATLPK4444D1ZKMay1644164412-01-202166150.00
472470TALLY06ATLPK4444D1ZKMay1642Jan-1642 12-01-202166150.00
482471TALLY06ATLPK4444D1ZKMay1643Jan-1643 12-01-202166150.00
492472TALLY06ATLPK4444D1ZKMay1644Jan-1644 12-01-202166150.00
501563TALLY29AADCF5555H1ZUJune15715707-07-2020355.00
5116PORTAL29AADCF5555H1ZUJune15715708-07-2020355.00
52800PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928-11-202021000.00
532229TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01-12-202021000.00
542608TALLY29AADCF5555H1ZUJulyRI/20-21/641Feb-641 09-02-202122722.00
551430PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110-02-202122722.00
561110PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123-12-202025284.00
572370TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23-12-202025284.00
581528TALLY33AAACY6666M1Z2AugustCBE0123115501-07-20202950.00
59417PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307-07-20202950.00
seperate error rows
Cell Formulas
RangeFormula
E10,E39,E26E10=F10
Click to expand...
First I would sort the data by column E and then remove the formats of the blank cells - clear all. Then sort back to original position . Select the data from first blank cell till the last row using control G and insert the formula.
 
Upvote 0
I don't understand why E10=335 (???)
Same for E16, E22, E26, E35, E39, E40 and others

M.
 
Upvote 0
Marcelo Branco said:
I don't understand why E10=335 (???)
Same for E16, E22, E26, E35, E39, E40 and others

M.
Click to expand...
This is accounting. Tally is the data what is entered in office. Portal is the data which is uploaded by the government. I have to check which bills have been entered and which are not in Portal. I could use pivot table and match it in seconds only if the invoice numbers, dates and amounts were the same. So I am trying to get the right invoice number first. E10 335 is in tally and not in portal. So once the formula is entered in the blank cells E10 has to show 335 only. If the tin number, trade name and amounts are matched, I could get the right invoice number in column E with the help of an index match function.
 
Upvote 0
I tried this function and got most of it right but some errors. I need to freeze some cells in the formula but how I don't know how
=INDEX(F2:F7,MATCH(E2,F2:F7,0))
 
Upvote 0
RAJESH1960 said:
I tried this function and got most of it right but some errors. I need to freeze some cells in the formula but how I don't know how
=INDEX(F2:F7,MATCH(E2,F2:F7,0))
Click to expand...
I inserted some blank rows in the top to get the range right. But I am getting some errors where portal invoice number is below the matching tally data. I have marked the mistakes in yellow. I need to add to the formula some additional information, but what no idea.
practice 2A match 23082021.xlsx
ABCDEFGH
1
2
3
4
5
6
7LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)
81PORTAL24BGZPS0000F1Z0January141404-09-20202950.00
92TALLY24BGZPS0000F1Z0January1446404-09-20202950.00
103PORTAL07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
114TALLY07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
125PORTAL07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
136TALLY07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
147PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819-01-20218118.60
158TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19-01-20218119.00
169TALLY27AHTPJ2222N1ZNMarchS-149833507-08-20208819.00
1710PORTAL27AHTPJ2222N1ZNMarchS-226S-22610-08-202015417.00
1811PORTAL27AHTPJ2222N1ZNMarchS-318S-31826-08-202015745.00
1912TALLY27AHTPJ2222N1ZNMarchS-31841926-08-202015745.00
2013PORTAL27AHTPJ2222N1ZNMarchS-303S-30324-08-202019727.00
2114TALLY27AHTPJ2222N1ZNMarchS-30340424-08-202019727.00
2215TALLY27AHTPJ2222N1ZNMarchS-30332706-08-202019733.00
2316PORTAL27AHTPJ2222N1ZNMarchS-206S-20606-08-202019744.00
2417PORTAL27AHTPJ2222N1ZNMarchS-736S-73621-10-202020300.00
2518TALLY27AHTPJ2222N1ZNMarchS-73673621-10-202020300.00
2619PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522-02-202120821.50
2720TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22-02-202120822.00
2821TALLY27AHTPJ2222N1ZNMarchS-1785Jan-1505 20-01-202121268.00
2922PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520-01-202121268.28
3023PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230-11-202021275.00
3124TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01-12-202021275.00
3225TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1235 15-12-202021322.00
3326PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113-09-202032422.00
3427TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113-09-202032422.00
3528PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120-03-202134550.00
3629TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820-03-202134550.00
3730PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103-09-202037845.00
3831TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303-09-202037845.00
3932PORTAL06ATLPK4444D1ZKMay1938193822-03-202112222.00
4033PORTAL06ATLPK4444D1ZKMay1939193922-03-202112222.00
4134TALLY06ATLPK4444D1ZKMay1939193922-03-202112222.00
4235TALLY06ATLPK4444D1ZKMay1939193822-03-202112222.00
4336PORTAL06ATLPK4444D1ZKMay58258219-09-202026775.00
4437TALLY06ATLPK4444D1ZKMay58255119-09-202026775.00
4538TALLY06ATLPK4444D1ZKMay58255319-09-202026775.00
4639TALLY06ATLPK4444D1ZKMay582Jan-1594 05-01-202126880.00
4740PORTAL06ATLPK4444D1ZKMay1594159408-01-202126880.00
4841PORTAL06ATLPK4444D1ZKMay1302130217-12-202027300.00
4942TALLY06ATLPK4444D1ZKMay1302Dec-1302 17-12-202027300.00
5043PORTAL06ATLPK4444D1ZKMay1642164212-01-202166150.00
5144PORTAL06ATLPK4444D1ZKMay1643164312-01-202166150.00
5245PORTAL06ATLPK4444D1ZKMay1644164412-01-202166150.00
5346TALLY06ATLPK4444D1ZKMay1644Jan-1642 12-01-202166150.00
5447TALLY06ATLPK4444D1ZKMay1644Jan-1643 12-01-202166150.00
5548TALLY06ATLPK4444D1ZKMay1644Jan-1644 12-01-202166150.00
5649TALLY29AADCF5555H1ZUJune164415707-07-2020355.00
5750PORTAL29AADCF5555H1ZUJune15715708-07-2020355.00
5851PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928-11-202021000.00
5952TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01-12-202021000.00
6053TALLY29AADCF5555H1ZUJulyRI/20-21/529Feb-641 09-02-202122722.00
6154PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110-02-202122722.00
6255PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123-12-202025284.00
6356TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23-12-202025284.00
6457TALLY33AAACY6666M1Z2AugustRI/20-21/571115501-07-20202950.00
6558PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307-07-20202950.00
seperate error rows
Cell Formulas
RangeFormula
E9,E63:E64,E59:E60,E53:E56,E49,E44:E46,E41:E42,E38,E36,E34,E31:E32,E27:E28,E25,E21:E22,E19,E15:E16,E13,E11E9=INDEX(F2:F14,MATCH(E8,F2:F14,0))
 
Last edited:
Upvote 0
RAJESH1960 said:
I inserted some blank rows in the top to get the range right. But I am getting some errors where portal invoice number is below the matching tally data. I have marked the mistakes in yellow. I need to add to the formula some additional information, but what no idea.
practice 2A match 23082021.xlsx
ABCDEFGH
1
2
3
4
5
6
7LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)
81PORTAL24BGZPS0000F1Z0January141404-09-20202950.00
92TALLY24BGZPS0000F1Z0January1446404-09-20202950.00
103PORTAL07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
114TALLY07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
125PORTAL07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
136TALLY07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
147PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819-01-20218118.60
158TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19-01-20218119.00
169TALLY27AHTPJ2222N1ZNMarchS-149833507-08-20208819.00
1710PORTAL27AHTPJ2222N1ZNMarchS-226S-22610-08-202015417.00
1811PORTAL27AHTPJ2222N1ZNMarchS-318S-31826-08-202015745.00
1912TALLY27AHTPJ2222N1ZNMarchS-31841926-08-202015745.00
2013PORTAL27AHTPJ2222N1ZNMarchS-303S-30324-08-202019727.00
2114TALLY27AHTPJ2222N1ZNMarchS-30340424-08-202019727.00
2215TALLY27AHTPJ2222N1ZNMarchS-30332706-08-202019733.00
2316PORTAL27AHTPJ2222N1ZNMarchS-206S-20606-08-202019744.00
2417PORTAL27AHTPJ2222N1ZNMarchS-736S-73621-10-202020300.00
2518TALLY27AHTPJ2222N1ZNMarchS-73673621-10-202020300.00
2619PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522-02-202120821.50
2720TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22-02-202120822.00
2821TALLY27AHTPJ2222N1ZNMarchS-1785Jan-1505 20-01-202121268.00
2922PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520-01-202121268.28
3023PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230-11-202021275.00
3124TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01-12-202021275.00
3225TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1235 15-12-202021322.00
3326PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113-09-202032422.00
3427TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113-09-202032422.00
3528PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120-03-202134550.00
3629TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820-03-202134550.00
3730PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103-09-202037845.00
3831TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303-09-202037845.00
3932PORTAL06ATLPK4444D1ZKMay1938193822-03-202112222.00
4033PORTAL06ATLPK4444D1ZKMay1939193922-03-202112222.00
4134TALLY06ATLPK4444D1ZKMay1939193922-03-202112222.00
4235TALLY06ATLPK4444D1ZKMay1939193822-03-202112222.00
4336PORTAL06ATLPK4444D1ZKMay58258219-09-202026775.00
4437TALLY06ATLPK4444D1ZKMay58255119-09-202026775.00
4538TALLY06ATLPK4444D1ZKMay58255319-09-202026775.00
4639TALLY06ATLPK4444D1ZKMay582Jan-1594 05-01-202126880.00
4740PORTAL06ATLPK4444D1ZKMay1594159408-01-202126880.00
4841PORTAL06ATLPK4444D1ZKMay1302130217-12-202027300.00
4942TALLY06ATLPK4444D1ZKMay1302Dec-1302 17-12-202027300.00
5043PORTAL06ATLPK4444D1ZKMay1642164212-01-202166150.00
5144PORTAL06ATLPK4444D1ZKMay1643164312-01-202166150.00
5245PORTAL06ATLPK4444D1ZKMay1644164412-01-202166150.00
5346TALLY06ATLPK4444D1ZKMay1644Jan-1642 12-01-202166150.00
5447TALLY06ATLPK4444D1ZKMay1644Jan-1643 12-01-202166150.00
5548TALLY06ATLPK4444D1ZKMay1644Jan-1644 12-01-202166150.00
5649TALLY29AADCF5555H1ZUJune164415707-07-2020355.00
5750PORTAL29AADCF5555H1ZUJune15715708-07-2020355.00
5851PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928-11-202021000.00
5952TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01-12-202021000.00
6053TALLY29AADCF5555H1ZUJulyRI/20-21/529Feb-641 09-02-202122722.00
6154PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110-02-202122722.00
6255PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123-12-202025284.00
6356TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23-12-202025284.00
6457TALLY33AAACY6666M1Z2AugustRI/20-21/571115501-07-20202950.00
6558PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307-07-20202950.00
seperate error rows
Cell Formulas
RangeFormula
E9,E63:E64,E59:E60,E53:E56,E49,E44:E46,E41:E42,E38,E36,E34,E31:E32,E27:E28,E25,E21:E22,E19,E15:E16,E13,E11E9=INDEX(F2:F14,MATCH(E8,F2:F14,0))
Click to expand...
Can I add something like it should accept the number only once in the range,,?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
108
Fluff
Fluff
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
585
juanam
J
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
380
kevin9999
K
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
324
awoohaw
awoohaw
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
439
felixstraube
F

Forum statistics

Threads
1,215,475
Messages
6,125,028
Members
449,205
Latest member
Eggy66

Share this page

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
Back
Top