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,"")
 
Sort by date Sort by votes
Marcelo Branco said:
Exact match with different dates??? Or shouldn't we take into account the dates?

M.
Click to expand...
The next step will be to match the dates using the same formula used for invoice numbers if I get it right. You can take amount into account. There are differences in amount ranging from 0.01 to 1/- plus or minus. That is why I suggested to use a a countifs function for the amounts '=COUNTIFS($H$2:$H$15000,H2)>1 within the formula.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
A countifs condition '=COUNTIFS($C:$D,$C1,$D:$D,$D1)>1 is added within the formula will help to get the repeated invoice numbers. But to edit the formula and adjust all these conditions I am no good at it.
 
Upvote 0
I don't understand what you mean by "The next step".
I'm really confused...
I'm afraid i'm not able to help you anymore.

M.
 
Upvote 0
Marcelo Branco said:
I don't understand what you mean by "The next step".
I'm really confused...
I'm afraid i'm not able to help you anymore.

M.
Click to expand...
Some of the dates are not matching, Hence after I match the invoice number I will have to match the dates too. It's a long process. But If i get the formula right for one then it is going to be easy to match all the other columns.
It's fine Marcelo Branco. Thanks for your time.
 
Upvote 0
My last shot :)

Pasta1
ABCDEFGH
1LineAs PerGSTIN of supplierNamecommonInvoice numberxxInvoice DateInvoice Value
223PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113/09/202032422,0
353TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113/09/202032422,0
424PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120/03/202134550,0
554TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820/03/202134550,0
625PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103/09/202037845,0
755TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303/09/202037845,0
830TALLY33AAACY6666M1Z2AugustCBE0123115501/07/20202950,0
92PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307/07/20202950,0
105PORTAL07EUDPS1111F1ZFFebruary2758275802/07/202010500,0
1134TALLY07EUDPS1111F1ZFFebruary2758275802/07/202010500,0
126PORTAL07EUDPS1111F1ZFFebruary3032303226/10/202010500,0
1335TALLY07EUDPS1111F1ZFFebruary2758303226/10/202010500,0
143PORTAL24BGZPS0000F1Z0January141404/09/20202950,0
1531TALLY24BGZPS0000F1Z0January1446404/09/20202950,0
1615PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928/11/202021000,0
1743TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01/12/202021000,0
1847TALLY29AADCF5555H1ZUJulyRI/20-21/641Feb-641 09/02/202122722,0
1918PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110/02/202122722,0
2019PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123/12/202025284,0
2148TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23/12/202025284,0
2229TALLY29AADCF5555H1ZUJune15715707/07/2020355,0
231PORTAL29AADCF5555H1ZUJune15715708/07/2020355,0
244PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819/01/20218118,6
2532TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19/01/20218119,0
2633TALLY27AHTPJ2222N1ZNMarch33533507/08/20208819,0
279PORTAL27AHTPJ2222N1ZNMarchS-226S-22610/08/202015417,0
2810PORTAL27AHTPJ2222N1ZNMarchS-318S-31826/08/202015745,0
2938TALLY27AHTPJ2222N1ZNMarchS-31841926/08/202015745,0
3011PORTAL27AHTPJ2222N1ZNMarchS-303S-30324/08/202019727,0
3139TALLY27AHTPJ2222N1ZNMarchS-30340424/08/202019727,0
3212PORTAL27AHTPJ2222N1ZNMarchS-206S-20606/08/202019733,0
3340TALLY27AHTPJ2222N1ZNMarchS-20632706/08/202019733,0
3413PORTAL27AHTPJ2222N1ZNMarchS-736S-73621/10/202020300,0
3541TALLY27AHTPJ2222N1ZNMarchS-73673621/10/202020300,0
3614PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522/02/202120821,5
3742TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22/02/202120822,0
3844TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505 20/01/202121268,0
3916PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520/01/202121268,3
4017PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230/11/202021275,0
4145TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01/12/202021275,0
4246TALLY27AHTPJ2222N1ZNMarchDec-1235 Dec-1235 15/12/202021322,0
437PORTAL06ATLPK4444D1ZKMay1938193822/03/202112222,0
448PORTAL06ATLPK4444D1ZKMay1939193922/03/202112222,0
4536TALLY06ATLPK4444D1ZKMay1938193922/03/202112222,0
4637TALLY06ATLPK4444D1ZKMay1938193822/03/202112222,0
4720PORTAL06ATLPK4444D1ZKMay58258219/09/202026775,0
4849TALLY06ATLPK4444D1ZKMay58255119/09/202026775,0
4950TALLY06ATLPK4444D1ZKMay58255319/09/202026775,0
5051TALLY06ATLPK4444D1ZKMay1594Jan-1594 05/01/202126880,0
5121PORTAL06ATLPK4444D1ZKMay1594159408/01/202126880,0
5222PORTAL06ATLPK4444D1ZKMay1302130217/12/202027300,0
5352TALLY06ATLPK4444D1ZKMay1302Dec-1302 17/12/202027300,0
5426PORTAL06ATLPK4444D1ZKMay1642164212/01/202166150,0
5527PORTAL06ATLPK4444D1ZKMay1643164312/01/202166150,0
5628PORTAL06ATLPK4444D1ZKMay1644164412/01/202166150,0
5756TALLY06ATLPK4444D1ZKMay1642Jan-1642 12/01/202166150,0
5857TALLY06ATLPK4444D1ZKMay1642Jan-1643 12/01/202166150,0
5958TALLY06ATLPK4444D1ZKMay1642Jan-1644 12/01/202166150,0
Plan6
Cell Formulas
RangeFormula
E2:E59E2=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$59,AGGREGATE(15,6,(ROW(F$2:F$59)-ROW(F$2)+1)/((B$2:B$59="PORTAL")*(C$2:C$59=C2)*(H$2:H$59>=H2-1)*(H$2:H$59<=H2+1)),1))),F2)


Hope you can find out a 100% solution.

Good luck

M.
 
Upvote 0
An improvement - this seems perfect to me ?

Pasta1
ABCDEFGH
1LineAs PerGSTIN of supplierNamecommonInvoice numberxxInvoice DateInvoice Value
223PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113/09/202032422,0
353TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113/09/202032422,0
424PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120/03/202134550,0
554TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820/03/202134550,0
625PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103/09/202037845,0
755TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303/09/202037845,0
830TALLY33AAACY6666M1Z2AugustCBE0123115501/07/20202950,0
92PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307/07/20202950,0
105PORTAL07EUDPS1111F1ZFFebruary2758275802/07/202010500,0
1134TALLY07EUDPS1111F1ZFFebruary2758275802/07/202010500,0
126PORTAL07EUDPS1111F1ZFFebruary3032303226/10/202010500,0
1335TALLY07EUDPS1111F1ZFFebruary3032303226/10/202010500,0
143PORTAL24BGZPS0000F1Z0January141404/09/20202950,0
1531TALLY24BGZPS0000F1Z0January1446404/09/20202950,0
1615PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928/11/202021000,0
1743TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01/12/202021000,0
1847TALLY29AADCF5555H1ZUJulyRI/20-21/641Feb-641 09/02/202122722,0
1918PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110/02/202122722,0
2019PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123/12/202025284,0
2148TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23/12/202025284,0
2229TALLY29AADCF5555H1ZUJune15715707/07/2020355,0
231PORTAL29AADCF5555H1ZUJune15715708/07/2020355,0
244PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819/01/20218118,6
2532TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19/01/20218119,0
2633TALLY27AHTPJ2222N1ZNMarch33533507/08/20208819,0
279PORTAL27AHTPJ2222N1ZNMarchS-226S-22610/08/202015417,0
2810PORTAL27AHTPJ2222N1ZNMarchS-318S-31826/08/202015745,0
2938TALLY27AHTPJ2222N1ZNMarchS-31841926/08/202015745,0
3011PORTAL27AHTPJ2222N1ZNMarchS-303S-30324/08/202019727,0
3139TALLY27AHTPJ2222N1ZNMarchS-30340424/08/202019727,0
3212PORTAL27AHTPJ2222N1ZNMarchS-206S-20606/08/202019733,0
3340TALLY27AHTPJ2222N1ZNMarchS-20632706/08/202019733,0
3413PORTAL27AHTPJ2222N1ZNMarchS-736S-73621/10/202020300,0
3541TALLY27AHTPJ2222N1ZNMarchS-73673621/10/202020300,0
3614PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522/02/202120821,5
3742TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22/02/202120822,0
3844TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505 20/01/202121268,0
3916PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520/01/202121268,3
4017PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230/11/202021275,0
4145TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01/12/202021275,0
4246TALLY27AHTPJ2222N1ZNMarchDec-1235 Dec-1235 15/12/202021322,0
437PORTAL06ATLPK4444D1ZKMay1938193822/03/202112222,0
448PORTAL06ATLPK4444D1ZKMay1939193922/03/202112222,0
4536TALLY06ATLPK4444D1ZKMay1938193922/03/202112222,0
4637TALLY06ATLPK4444D1ZKMay1939193822/03/202112222,0
4720PORTAL06ATLPK4444D1ZKMay58258219/09/202026775,0
4849TALLY06ATLPK4444D1ZKMay58255119/09/202026775,0
4950TALLY06ATLPK4444D1ZKMay55355319/09/202026775,0
5051TALLY06ATLPK4444D1ZKMay1594Jan-1594 05/01/202126880,0
5121PORTAL06ATLPK4444D1ZKMay1594159408/01/202126880,0
5222PORTAL06ATLPK4444D1ZKMay1302130217/12/202027300,0
5352TALLY06ATLPK4444D1ZKMay1302Dec-1302 17/12/202027300,0
5426PORTAL06ATLPK4444D1ZKMay1642164212/01/202166150,0
5527PORTAL06ATLPK4444D1ZKMay1643164312/01/202166150,0
5628PORTAL06ATLPK4444D1ZKMay1644164412/01/202166150,0
5756TALLY06ATLPK4444D1ZKMay1642Jan-1642 12/01/202166150,0
5857TALLY06ATLPK4444D1ZKMay1643Jan-1643 12/01/202166150,0
5958TALLY06ATLPK4444D1ZKMay1644Jan-1644 12/01/202166150,0
Plan6
Cell Formulas
RangeFormula
E2:E59E2=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$59,AGGREGATE(15,6,(ROW(F$2:F$59)-ROW(F$2)+1)/((B$2:B$59="PORTAL")*(C$2:C$59=C2)*(H$2:H$59>=H2-1)*(H$2:H$59<=H2+1)),MAX(1,COUNTIFS(B$2:B2,"TALLY",C$2:C2,C2,H$2:H2,">="&H2-1,H$2:H2,"<="&H2+1))))),F2)


M.
 
Upvote 0
Solution
Marcelo Branco said:
My last shot :)

Pasta1
ABCDEFGH
1LineAs PerGSTIN of supplierNamecommonInvoice numberxxInvoice DateInvoice Value
223PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113/09/202032422,0
353TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113/09/202032422,0
424PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120/03/202134550,0
554TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820/03/202134550,0
625PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103/09/202037845,0
755TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303/09/202037845,0
830TALLY33AAACY6666M1Z2AugustCBE0123115501/07/20202950,0
92PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307/07/20202950,0
105PORTAL07EUDPS1111F1ZFFebruary2758275802/07/202010500,0
1134TALLY07EUDPS1111F1ZFFebruary2758275802/07/202010500,0
126PORTAL07EUDPS1111F1ZFFebruary3032303226/10/202010500,0
1335TALLY07EUDPS1111F1ZFFebruary2758303226/10/202010500,0
143PORTAL24BGZPS0000F1Z0January141404/09/20202950,0
1531TALLY24BGZPS0000F1Z0January1446404/09/20202950,0
1615PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928/11/202021000,0
1743TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01/12/202021000,0
1847TALLY29AADCF5555H1ZUJulyRI/20-21/641Feb-641 09/02/202122722,0
1918PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110/02/202122722,0
2019PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123/12/202025284,0
2148TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23/12/202025284,0
2229TALLY29AADCF5555H1ZUJune15715707/07/2020355,0
231PORTAL29AADCF5555H1ZUJune15715708/07/2020355,0
244PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819/01/20218118,6
2532TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19/01/20218119,0
2633TALLY27AHTPJ2222N1ZNMarch33533507/08/20208819,0
279PORTAL27AHTPJ2222N1ZNMarchS-226S-22610/08/202015417,0
2810PORTAL27AHTPJ2222N1ZNMarchS-318S-31826/08/202015745,0
2938TALLY27AHTPJ2222N1ZNMarchS-31841926/08/202015745,0
3011PORTAL27AHTPJ2222N1ZNMarchS-303S-30324/08/202019727,0
3139TALLY27AHTPJ2222N1ZNMarchS-30340424/08/202019727,0
3212PORTAL27AHTPJ2222N1ZNMarchS-206S-20606/08/202019733,0
3340TALLY27AHTPJ2222N1ZNMarchS-20632706/08/202019733,0
3413PORTAL27AHTPJ2222N1ZNMarchS-736S-73621/10/202020300,0
3541TALLY27AHTPJ2222N1ZNMarchS-73673621/10/202020300,0
3614PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522/02/202120821,5
3742TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22/02/202120822,0
3844TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505 20/01/202121268,0
3916PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520/01/202121268,3
4017PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230/11/202021275,0
4145TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01/12/202021275,0
4246TALLY27AHTPJ2222N1ZNMarchDec-1235 Dec-1235 15/12/202021322,0
437PORTAL06ATLPK4444D1ZKMay1938193822/03/202112222,0
448PORTAL06ATLPK4444D1ZKMay1939193922/03/202112222,0
4536TALLY06ATLPK4444D1ZKMay1938193922/03/202112222,0
4637TALLY06ATLPK4444D1ZKMay1938193822/03/202112222,0
4720PORTAL06ATLPK4444D1ZKMay58258219/09/202026775,0
4849TALLY06ATLPK4444D1ZKMay58255119/09/202026775,0
4950TALLY06ATLPK4444D1ZKMay58255319/09/202026775,0
5051TALLY06ATLPK4444D1ZKMay1594Jan-1594 05/01/202126880,0
5121PORTAL06ATLPK4444D1ZKMay1594159408/01/202126880,0
5222PORTAL06ATLPK4444D1ZKMay1302130217/12/202027300,0
5352TALLY06ATLPK4444D1ZKMay1302Dec-1302 17/12/202027300,0
5426PORTAL06ATLPK4444D1ZKMay1642164212/01/202166150,0
5527PORTAL06ATLPK4444D1ZKMay1643164312/01/202166150,0
5628PORTAL06ATLPK4444D1ZKMay1644164412/01/202166150,0
5756TALLY06ATLPK4444D1ZKMay1642Jan-1642 12/01/202166150,0
5857TALLY06ATLPK4444D1ZKMay1642Jan-1643 12/01/202166150,0
5958TALLY06ATLPK4444D1ZKMay1642Jan-1644 12/01/202166150,0
Plan6
Cell Formulas
RangeFormula
E2:E59E2=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$59,AGGREGATE(15,6,(ROW(F$2:F$59)-ROW(F$2)+1)/((B$2:B$59="PORTAL")*(C$2:C$59=C2)*(H$2:H$59>=H2-1)*(H$2:H$59<=H2+1)),1))),F2)


Hope you can find out a 100% solution.

Good luck

M.
Click to expand...
Marcelo Branco. You have solved 90% of my problem. Thank you so so much. I will try to figure out some way and make it 100%. Thanks once again.
 
Upvote 0
RAJESH1960 said:
Marcelo Branco. You have solved 90% of my problem. Thank you so so much. I will try to figure out some way and make it 100%. Thanks once again.
Click to expand...
Marcelo. Your last shot was the best shot. I posted the formula in the data base, checked each entry and guess what... I got it 100% accurate. I changed the references in the formula, pasted in a new row to match dates and got the dates too matched. Your one formula helped me to finish the work very fast. The work which would take at least 5 days to complete, I completed in just 5 minutes with the help of your formula. You really are a genius man. Thanks a TON BOSS.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
286
excelnewbie999
E
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
232
Raj1979
R
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
248
dshtesy
D
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
172
sanrama
S
S
  • Solved
Count of active employees between dates.
Replies
3
Views
634
Fluff
Fluff

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

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