Index Match function List based on date

M

mba_110

Board Regular
Joined
Nov 28, 2012
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I am trying to pull out list of my cheque deposits based on date it has been deposited, i have full list of deposits but i want only list based on the date i have provided on Daily Cheques Deposit sheet.

Since, we deposit multiple cheques in same date so, i am unable to pull out all those cheques that are matching the date provided on Daily cheques Deposit sheet.

This is the date i need based on the above date.
Cheque Deposits.xlsx
BCDEFG
1
2CHEQUE DEPOSIT REPORT
3DATE :2 Sep 2024
4
5CHEQUE DATECHEQUE NOCUSTOMER NAMECHEQUE AMOUNTDEPOSIT DATEBANK
6
7
8
9
10
11
12
13
14
15
16
Daily Cheque Deposits
Cell Formulas
RangeFormula
G3G3=TODAY()


This is the full data from which we have to pull out the above list based on the date mentioned there.
Cheque Deposits.xlsx
BCDEFGHIJKLM
6S.NOCHEQUE DETAILSDEPOSIT DETAILS
7CHEQUE DATECUSTOMER NAMERECEIVED DATECHEQUE NO CHEQUE AMOUNT RECEIVED BYREMARKSDEPOSIT DATEBANKDEPOSIT BY
8
925 Apr 2023EVE Medical Center LLC7 Oct 2022Chq# 000135100.00Chq Date: 25/04/20233 Nov 2023SIB
1025 May 2023EVE Medical Center LLC7 Oct 2022Chq# 000136105.00Chq Date:25-May-20234 Nov 2023SIB
1125 Jul 2023EVE Medical Center LLC20 Oct 2022Chq# 386110.00Chq Date: 25-Jul-2023 postponed till 20/105 Nov 2023SIB
1225 May 2023EVE Medical Center LLC28 Oct 2022Chq#388115.00Chq Date: 25-May-20236 Nov 2023RAK
1325 Jun 2023EVE Medical Center LLC28 Oct 2022Chq# 387120.00Chq Date: 25-Jun-20237 Nov 2023RAK
1425 Apr 2023EVE Medical Center LLC28 Oct 2022Chq# 389125.00Chq Date: 25-Apr-20232 Sep 2024RAK
1530 Aug 2023EVE Medical Center LLC30 Oct 2022Chq# 000593130.00Chq Date: 30-Aug-2023 postponed till 30/1029 Oct 2023RAK
1630 Apr 2023EVE Medical Center LLC27 Nov 2022Chq# 000589135.00Chq Date:30-Apr-202330 Oct 2023RAK
1730 Jun 2023EVE Medical Center LLC27 Nov 2022Chq# 000591140.00Chq Date: 30-Jun-202331 Oct 2023RAK
1830 Sep 2023EVE Medical Center LLC27 Nov 2022Chq# 000594145.00Chq Date: 30-Sep-20231 Nov 2023RAK
1930 May 2023EVE Medical Center LLC27 Nov 2022Chq# 000590150.00Chq Date: 30-May-20232 Nov 2023RAK
2025 May 2023EVE Medical Center LLC23 Dec 2022Chq# 728155.00Chq Date: 25-Jun-20233 Nov 2023SIB
2125 May 2023EVE Medical Center LLC23/12/2022Chq# 733160.00Chq Date: 25-May-20234 Nov 2023SIB
2225 Jun 2023EVE Medical Center LLC23/12/2022Chq# 734165.00Chq Date: 25-Jun-20235 Nov 2023SIB
2325 May 2023EVE Medical Center LLC23/12/2022Chq# 727170.00Chq Date:25-May-20236 Nov 2023SIB
2425 Jun 2023EVE Medical Center LLC27/12/2022Chq# 729175.00Chq Date: 25-Jun-20237 Nov 2023SIB
2525 May 2023EVE Medical Center LLC27/12/2022Chq# 732180.00Chq Date: 25-May-20238 Nov 2023SIB
2625 Jun 2023EVE Medical Center LLC07/01/2023Chq# 851185.00Chq Date: 25-Jun-2023 PDC Against Inv# 55119 Nov 2023SIB
2725 May 2023EVE Medical Center LLC07/01/2023Chq# 850190.00Chq Date: 25-May-2023 PDC Against Inv# 55112 Sep 2024SIB
2825 Jun 2023EVE Medical Center LLC07/01/2023Chq#849195.00Chq Date: 25-Jun-2023 PDC Against Inv# 55136 Jan 2024SIB
2920 Mar 2023Cyrus Poly Clinic19/01/2023Chq# 000091200.00Chq Date:20-Mar-2023 Payment Against Inv# 54827 Jan 2024SIB
302 Sep 2024Cyrus Poly Clinic31/01/2023Chq# 100205.00PDC Received against inv# 5434,5448,5473 and 54078 Jan 2024SIB
319 Jun 2023Monroe Medical Center05/02/2023Chq# 1812210.00PDC Dated: 09-Jun-2023 Received Against Inv# 55959 Jan 2024SIB
3225 Jun 2023EVE Medical Center LLC07/02/2023Chq#000853215.00PDC Dated 25-Jun-2023 Received Against Inv# 55122 Sep 2024SIB
3330 Aug 2023Medhills Medical Centre LLC09/02/2023Chq# 161220.00PDC Dated 30-Aug-2023 Received Against Inv# 558411 Jan 2024SIB
3430 Jun 2023Medhills Medical Centre LLC09/02/2023Chq# 163225.00PDC Dated 30-Jun-2023 Received Against Inv# 517612 Jan 2024SIB
3523 Jun 2023Yaqin Medical Center13/02/2023Chq#277230.00PDC Dated 23-Jun-2023 Received against Inv# 5272 and 540013 Jan 2024SIB
3617 Jul 2023Yaqin Medical Center13/02/2023Chq# 278235.00PDC Dated 17-Jul-2023 Received Against Inv# 543714 Jan 2024SIB
372 Sep 2024Yaqin Medical Center13/02/2023Chq# 279240.00PDC Dated 24-Jul-2023 Received Against Inv# 544415 Jan 2024SIB
3823 Aug 2023Yaqin Medical Center13/02/2023Chq# 281245.00PDC Dated 23-Aug-2023 Received Against Inv#5576416 Jan 2024SIB
3915 Jun 20237D Medical Center LLC - Sole Proprietorship LLC13/02/2023Chq# 965250.00PDC Dated 15-Jun-2023 Received Against Inv# 56562 Sep 2024SIB
4015 Jun 2023Yaqin Medical Center13/02/2023Chq# 276255.00PDC Dated 15-Jun-2023 Received Against Inv# 52722 Sep 2024SIB
415 Aug 2023Smile Care Medical Center LLC01/03/2023Chq#566739260.00Chq dtd 05.Aug.202316 Jan 2024SIB
422 Sep 2024Smile Care Medical Center LLC01/03/2023Chq#566737265.00Cheque dated 05.06.2317 Jan 2024SIB
4330 May 2023Los Angeles Aesthetic Medical Center06/03/2023Chq#003296270.00Chq dated 30.05.202318 Jan 2024SIB
4430 May 2023Los Angeles Aesthetic Medical Center06/03/2023Chq#003296275.00chq dated 30.05.2319 Jan 2024SIB
4526 May 2023Los Angeles Aesthetic Medical Center06/03/2023Chq#003302280.00Chq dated 26.May.202320 Jan 2024SIB
4626 May 2023Los Angeles Aesthetic Medical Center06/03/2023Chq#003302285.00chq dated 26.05.2321 Jan 2024RAK
4725 Jun 2023Hoor Al Aliaa Polyclinic07/03/2023Chq#001758290.00Chq dated 25.06.20232 Sep 2024RAK
487 Jun 2023Clinica Medical Center09/03/2023Chq#001582295.00chq dated 07.06.202321 Jan 2024RAK
49Yaqin Medical Center14/03/2023Chq#000289300.0022 Jan 2024RAK
5025 Jun 2023Cornish Arabian Aesthetic Clinic16/03/2023Chq# 000334305.00PDC Dated: 25-Jun-2023 Received Against Inv# 578923 Jan 2024RAK
5113 Jun 2023Los Angeles Aesthetic Medical Center28/03/2023Chq#003380310.00chq dated 13.06.2324 Jan 2024RAK
522 Sep 2024Los Angeles Aesthetic Medical Center28/03/2023Chq#003381315.00chq dated 09.06.2325 Jan 2024RAK
532 Sep 2024Las Vegas Aesthetic Medical Center LLC28/03/2023chq#003371320.00chq dated 07.06.232 Sep 2024RAK
5420 May 2023Al Andalus Medical Center03/04/2023Chq#002504325.00Chq dated 20.May.202325 Jan 2024RAK
55
56
57TOTAL CHEQUE AMOUNT :9,775.00
Cheque Report
Cell Formulas
RangeFormula
G57G57=SUM(G9:G56)


Any Help will be appreciated.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hello, please test this - just make sure first that the headers of both tables are the same (at the moment there are some unnecessary spaces):

Excel Formula: 
=CHOOSECOLS(FILTER('Cheque Report'!C9:L54,'Cheque Report'!K9:K54='Daily Cheque Deposits'!G3),XMATCH(B5:G5,'Cheque Report'!C7:L7))
 
Upvote 0
hagia_sofia said:
Hello, please test this - just make sure first that the headers of both tables are the same (at the moment there are some unnecessary spaces):

Excel Formula: 
=CHOOSECOLS(FILTER('Cheque Report'!C9:L54,'Cheque Report'!K9:K54='Daily Cheque Deposits'!G3),XMATCH(B5:G5,'Cheque Report'!C7:L7))
Click to expand...
Thanks for your reply, but its is not working when i do new entry in Cheque Report sheet its not listing that in second sheet Daily Cheque Deposits.
I have large list and i keep updating it every day, i need strong formula that should work even i have blanks in middle. thanks.
 
Upvote 0
mba_110 said:
Thanks for your reply, but its is not working when i do new entry in Cheque Report sheet its not listing that in second sheet Daily Cheque Deposits.
I have large list and i keep updating it every day, i need strong formula that should work even i have blanks in middle. thanks.
Click to expand...

Hello, if you transform the "Cheque Report" (CTRL + T) table into an official Excel table it should work, i.e. auto-update once you add new rows.
 
Upvote 0
hagia_sofia said:
Hello, if you transform the "Cheque Report" (CTRL + T) table into an official Excel table it should work, i.e. auto-update once you add new rows.
Click to expand...
This look unreliable method, even after transformation to table it's not working. Index Match can help? i need data from particular columns with same date value as G3 in (Daily Cheque Deposits) ?
 
Upvote 0
How about
Fluff.xlsm
ABCDEFG
1
2CHEQUE DEPOSIT REPORT
3DATE :02/09/2024
4
5CHEQUE DATECHEQUE NOCUSTOMER NAMECHEQUE AMOUNTDEPOSIT DATEBANK
625/04/2023Chq# 389EVE Medical Center LLC12502/09/2024RAK
725/05/2023Chq# 850EVE Medical Center LLC19002/09/2024SIB
825/06/2023Chq#000853EVE Medical Center LLC21502/09/2024SIB
915/06/2023Chq# 9657D Medical Center LLC - Sole Proprietorship LLC25002/09/2024SIB
1015/06/2023Chq# 276Yaqin Medical Center25502/09/2024SIB
1125/06/2023Chq#001758Hoor Al Aliaa Polyclinic29002/09/2024RAK
1202/09/2024chq#003371Las Vegas Aesthetic Medical Center LLC32002/09/2024RAK
13
Data
Cell Formulas
RangeFormula
G3G3=TODAY()
B6:G12B6=CHOOSECOLS(FILTER('Cheque Report'!C8:L1000,'Cheque Report'!K8:K1000=G3),1,4,2,5,9,10)
Dynamic array formulas.
 
Upvote 0
Fluff said:
How about
Fluff.xlsm
ABCDEFG
1
2CHEQUE DEPOSIT REPORT
3DATE :02/09/2024
4
5CHEQUE DATECHEQUE NOCUSTOMER NAMECHEQUE AMOUNTDEPOSIT DATEBANK
625/04/2023Chq# 389EVE Medical Center LLC12502/09/2024RAK
725/05/2023Chq# 850EVE Medical Center LLC19002/09/2024SIB
825/06/2023Chq#000853EVE Medical Center LLC21502/09/2024SIB
915/06/2023Chq# 9657D Medical Center LLC - Sole Proprietorship LLC25002/09/2024SIB
1015/06/2023Chq# 276Yaqin Medical Center25502/09/2024SIB
1125/06/2023Chq#001758Hoor Al Aliaa Polyclinic29002/09/2024RAK
1202/09/2024chq#003371Las Vegas Aesthetic Medical Center LLC32002/09/2024RAK
13
Data
Cell Formulas
RangeFormula
G3G3=TODAY()
B6:G12B6=CHOOSECOLS(FILTER('Cheque Report'!C8:L1000,'Cheque Report'!K8:K1000=G3),1,4,2,5,9,10)
Dynamic array formulas.
Click to expand...
Yeah, you nailed it, exactly the result i wanted, I owe you a treat.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
How to make this IF formula work?
Replies
1
Views
203
Fluff
Fluff
B
  • Question
Percent Change
Replies
4
Views
160
kvsrinivasamurthy
K
S
  • Solved
lookup return from range of dates
Replies
4
Views
465
Peter_SSs
Peter_SSs
B
  • Question
calculate monthly value
Replies
0
Views
97
BrutalLogiC
B
vinzent
  • Question
Formula to get the 4th workday before end of month
Replies
8
Views
380
Tetra201
Tetra201

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

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