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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
339
excelnewbie999
E
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
215
sanrama
S

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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