Lookup and Matching Invoices

[yoosh]

Hi everyone!

Excel version: 2013

I am using Excel to keep a checkbook of my workplace, specifically for tracking expenses.

I am trying to figure out a formula that will pull invoice data based on a cell I have that has the current month in it (January). I have three different worksheets - Operating Expenses, Admin Expenses, and Advertising Expenses. Each of the invoices are broken up into spend categories such as cleaning supplies, uniforms, internet, etc.

I have all of my invoices on one worksheet with the following data in columns - invoice date, spend category, and details.

I would like the formula to match the invoice dates and spend categories for the current month (which is picked by a drop-down cell), but I only want to pull the invoices for the spend categories on that worksheet (operating expenses only, for example).

I've attached an example of the Operating Expenses worksheet. The other worksheets look very similar with different spend categories at the top.

Thank you so much for your help. I've spent so much time on this spreadsheet but am limited because of my excel knowledge.

 

[Toadstool]

Hi Yoosh,

You will also need the year (which I added as a dropdown) otherwise your numbers won't work over year end.

I didn't see a value mentioned on the Invoices so I've added that as column D.

Is this what you wanted?

Yoosh.xlsx
	A	B	C	D
1	Invoice Date	Spend Category	Details	Value
2	01-Jan-21	Cleaning Supplies	Rags	$1,200.00
3	07-Feb-21	Uniforms	Dickies	$3,200.00
4	09-Feb-21	Internet	AT&T	$400.00
5	11-Feb-21	Wages	Less O/T	$23,000.00
6	13-Feb-21	Phone	AT&T	$750.00
7	15-Feb-21	Bribe	Phebius Project	$50,000.00
8	17-Feb-21	Hitman Contract	CEO Phebius	$120,000.00
9	18-Feb-21	Blackmail payment	Witness	$35,000.00
10	19-Feb-21	Cleaning Supplies	Rags	$1,200.00
11	20-Feb-21	Uniforms	Dickies	$3,200.00
12	21-Feb-21	Internet	AT&T	$400.00
13	22-Feb-21	Wages	Less O/T	$23,000.00
14	22-Feb-21	Wages	O/T	$8,500.00
15	23-Feb-21	Phone	AT&T	$750.00
16	24-Feb-21	Bribe	Phebius CFO	$88,000.00
17	03-Mar-21	Bribe	Phebius CIO	$98,000.00
Invoices

Yoosh.xlsx
	A	B	C	D	E	F	G	H	I
1	Check Book Accounting		February	2021	Cleaning Supplies	Uniforms	Internet	Wages	Phone
2		For month starting	01-Feb-21
3					Total	Total	Total	Total	Total
4	Date	Spend Category	Invoice Detail Items Purchased		$1,200.00	$6,400.00	$800.00	$54,500.00	$1,500.00
5	07-Feb-21	Uniforms	Dickies			$3,200.00
6	09-Feb-21	Internet	AT&T				$400.00
7	11-Feb-21	Wages	Less O/T					$23,000.00
8	13-Feb-21	Phone	AT&T						$750.00
9	19-Feb-21	Cleaning Supplies	Rags		$1,200.00
10	20-Feb-21	Uniforms	Dickies			$3,200.00
11	21-Feb-21	Internet	AT&T				$400.00
12	22-Feb-21	Wages	Less O/T					$23,000.00
13	22-Feb-21	Wages	O/T					$8,500.00
14	23-Feb-21	Phone	AT&T						$750.00
15
Operating Expense

Cell Formulas
Range	Formula
C2	C2	=DATE($D$1,MATCH($C$1,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1)
E4:I4	E4	=SUM(E$5:E$9999)
E5:I15	E5	=IFERROR(IF(E$1=INDEX(Invoices!$B$2:$B$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=$C$2)*(Invoices!$A$2:$A$9999<=EOMONTH($C$2,0))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),INDEX(Invoices!$D$2:$D$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=$C$2)*(Invoices!$A$2:$A$9999<=EOMONTH($C$2,0))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),""),"")
A5:C15	A5	=IFERROR(INDEX(Invoices!A$2:A$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=$C$2)*(Invoices!$A$2:$A$9999<=EOMONTH($C$2,0))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),"")

Cells with Data Validation
Cell	Allow	Criteria
C1	List	January,February,March,April,May,June,July,August,September,October,November,December
D1	List	2018,2019,2020,2021,2022,2023,2024,2025

 

[yoosh]

Hey! Thank you so much!

The formula is not working for me. Does it matter which order the columns are in? Specifically in the invoice worksheet?

 

[Toadstool]

Yes, of course. You didn't say what the format was so I went with

If you give me your format I can amend as necessary.

 

[yoosh]

Thank you so much Toadstool!! The formula works now. The only issue I have now is that the start date of the month periods are not actually the first of the month. For example, there are some days in February that run on the January accounting period. How do I fix the formula to account for this?

 

[Toadstool]

Do you have a table of the dates/Accounting Periods?
Please supply that table or the mechanism you use to calculate Accounting Period.

 

[yoosh]

Do you have a table of the dates/Accounting Periods?
Please supply that table or the mechanism you use to calculate Accounting Period.

Hi sorry for the delay. I had to wait until today to get the correct information from accounting.

The following table has out accounting periods.

Month	First Day of Month*	Month-End Date*	Number of Weeks
January	01/03/22	01/30/22	4
February	01/31/22	02/27/22	4
March	02/28/22	04/03/22	5
April	04/04/22	05/01/22	4
May	05/02/22	05/29/22	4
June	05/30/22	07/03/22	5
July	07/04/22	07/31/22	4
August	08/01/22	08/28/22	4
September	08/29/22	10/02/22	5
October	10/03/22	10/30/22	4
November	10/31/22	11/27/22	4
December	11/28/22	01/01/23	5

 

[Toadstool]

So I've added the accounting periods table as a new sheet "ACPeriods"

Yoosh2.xlsx
	A	B	C	D
1	Month	First Day of Month*	Month-End Date*	Number of Weeks
2
3	January	03-Jan-22	30-Jan-22	4
4	February	31-Jan-22	27-Feb-22	4
5	March	28-Feb-22	03-Apr-22	5
6	April	04-Apr-22	01-May-22	4
7	May	02-May-22	29-May-22	4
8	June	30-May-22	03-Jul-22	5
9	July	04-Jul-22	31-Jul-22	4
10	August	01-Aug-22	28-Aug-22	4
11	September	29-Aug-22	02-Oct-22	5
12	October	03-Oct-22	30-Oct-22	4
13	November	31-Oct-22	27-Nov-22	4
14	December	28-Nov-22	01-Jan-23	5
ACPeriods

I updated my Invoices to be 2022 dates

Yoosh2.xlsx
	A	B	C	D
1	Invoice Date	Spend Category	Details	Value
2	01-Jan-22	Cleaning Supplies	Rags	$1,200.00
3	31-Jan-22	Cleaning Supplies	Bleach	$125.00
4	07-Feb-22	Uniforms	Dickies	$3,200.00
5	09-Feb-22	Internet	AT&T	$400.00
6	11-Feb-22	Wages	Less O/T	$23,000.00
7	13-Feb-22	Phone	AT&T	$750.00
8	15-Feb-22	Bribe	Phebius Project	$50,000.00
9	17-Feb-22	Hitman Contract	CEO Phebius	$120,000.00
10	18-Feb-22	Blackmail payment	Witness	$35,000.00
11	19-Feb-22	Cleaning Supplies	Rags	$1,200.00
12	20-Feb-22	Uniforms	Dickies	$3,200.00
13	22-Feb-22	Internet	AT&T	$400.00
14	22-Feb-22	Wages	Less O/T	$23,000.00
15	22-Feb-22	Wages	O/T	$8,500.00
16	23-Feb-22	Phone	AT&T	$750.00
17	24-Feb-22	Bribe	Phebius CFO	$88,000.00
18	03-Mar-22	Bribe	Phebius CIO	$98,000.00
19
Invoices

Here's the revised Operating Expense sheet

Yoosh2.xlsx
	A	B	C	D	E	F	G	H	I
1	Check Book Accounting		February		Cleaning Supplies	Uniforms	Internet	Wages	Phone
2
3					Total	Total	Total	Total	Total
4	Date	Spend Category	Invoice Detail Items Purchased		$1,325.00	$6,400.00	$800.00	$54,500.00	$1,500.00
5	31-Jan-22	Cleaning Supplies	Bleach		$125.00
6	07-Feb-22	Uniforms	Dickies			$3,200.00
7	09-Feb-22	Internet	AT&T				$400.00
8	11-Feb-22	Wages	Less O/T					$23,000.00
9	13-Feb-22	Phone	AT&T						$750.00
10	19-Feb-22	Cleaning Supplies	Rags		$1,200.00
11	20-Feb-22	Uniforms	Dickies			$3,200.00
12	22-Feb-22	Internet	AT&T				$400.00
13	22-Feb-22	Wages	Less O/T					$23,000.00
14	22-Feb-22	Wages	O/T					$8,500.00
15	23-Feb-22	Phone	AT&T						$750.00
16
Operating Expense

Cell Formulas
Range	Formula
E4:I4	E4	=SUM(E$5:E$9999)
E5:I16	E5	=IFERROR(IF(E$1=INDEX(Invoices!$B$2:$B$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=INDEX(ACPeriods!$B$3:$B$14,MATCH($C$1,ACPeriods!$A$3:$A$14,0)))*(Invoices!$A$2:$A$9999<=INDEX(ACPeriods!$C$3:$C$14,MATCH($C$1,ACPeriods!$A$3:$A$14,0)))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),INDEX(Invoices!$D$2:$D$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=INDEX(ACPeriods!$B$3:$B$14,MATCH($C$1,ACPeriods!$A$3:$A$14,0)))*(Invoices!$A$2:$A$9999<=INDEX(ACPeriods!$C$3:$C$14,MATCH($C$1,ACPeriods!$A$3:$A$14,0)))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),""),"")
A5:C16	A5	=IFERROR(INDEX(Invoices!A$2:A$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=INDEX(ACPeriods!$B$3:$B$14,MATCH($C$1,ACPeriods!$A$3:$A$14,0)))*(Invoices!$A$2:$A$9999<=INDEX(ACPeriods!$C$3:$C$14,MATCH($C$1,ACPeriods!$A$3:$A$14,0)))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),"")

Cells with Data Validation
Cell	Allow	Criteria
C1	List	January,February,March,April,May,June,July,August,September,October,November,December

 

[yoosh]

Do I not need the drop box for the year any more? I guess I could just make a new spreadsheet for next year.

 

[Toadstool]

Do I not need the drop box for the year any more? I guess I could just make a new spreadsheet for next year.

Your ACPeriods are specific to a year so "January" can only mean January 2022.

If you want it to be more flexible, and handle 2021, then I suggest you have Accounting supply the 2021 periods and embed the financial year in the month selection dropdown. That would look something like this (Note: My 2021 dates are fictitious, of course).

Yoosh3.xlsx
	A	B	C
1	Month	First Day of Month*	Month-End Date*
2
3	January FY21	03-Jan-21	30-Jan-21
4	February FY21	31-Jan-21	27-Feb-21
5	March FY21	28-Feb-21	03-Apr-21
6	April FY21	04-Apr-21	01-May-21
7	May FY21	02-May-21	29-May-21
8	June FY21	30-May-21	03-Jul-21
9	July FY21	04-Jul-21	31-Jul-21
10	August FY21	01-Aug-21	28-Aug-21
11	September FY21	29-Aug-21	02-Oct-21
12	October FY21	03-Oct-21	30-Oct-21
13	November FY21	31-Oct-21	27-Nov-21
14	December FY21	28-Nov-21	01-Jan-22
15	January FY22	03-Jan-22	30-Jan-22
16	February FY22	31-Jan-22	27-Feb-22
17	March FY22	28-Feb-22	03-Apr-22
18	April FY22	04-Apr-22	01-May-22
19	May FY22	02-May-22	29-May-22
20	June FY22	30-May-22	03-Jul-22
21	July FY22	04-Jul-22	31-Jul-22
22	August FY22	01-Aug-22	28-Aug-22
23	September FY22	29-Aug-22	02-Oct-22
24	October FY22	03-Oct-22	30-Oct-22
25	November FY22	31-Oct-22	27-Nov-22
26	December FY22	28-Nov-22	01-Jan-23
27
ACPeriods

Then the Operating Expense sheet changes to this:

Yoosh3.xlsx
	A	B	C	D	E	F	G	H	I
1	Check Book Accounting		February FY22		Cleaning Supplies	Uniforms	Internet	Wages	Phone
2
3					Total	Total	Total	Total	Total
4	Date	Spend Category	Invoice Detail Items Purchased		$1,325.00	$6,400.00	$800.00	$54,500.00	$1,500.00
5	31-Jan-22	Cleaning Supplies	Bleach		$125.00
6	07-Feb-22	Uniforms	Dickies			$3,200.00
7	09-Feb-22	Internet	AT&T				$400.00
8	11-Feb-22	Wages	Less O/T					$23,000.00
9	13-Feb-22	Phone	AT&T						$750.00
10	19-Feb-22	Cleaning Supplies	Rags		$1,200.00
11	20-Feb-22	Uniforms	Dickies			$3,200.00
12	22-Feb-22	Internet	AT&T				$400.00
13	22-Feb-22	Wages	Less O/T					$23,000.00
14	22-Feb-22	Wages	O/T					$8,500.00
15	23-Feb-22	Phone	AT&T						$750.00
16
Operating Expense

Cell Formulas
Range	Formula
E4:I4	E4	=SUM(E$5:E$9999)
E5:I16	E5	=IFERROR(IF(E$1=INDEX(Invoices!$B$2:$B$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=INDEX(ACPeriods!$B$3:$B$51,MATCH($C$1,ACPeriods!$A$3:$A$51,0)))*(Invoices!$A$2:$A$9999<=INDEX(ACPeriods!$C$3:$C$51,MATCH($C$1,ACPeriods!$A$3:$A$51,0)))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),INDEX(Invoices!$D$2:$D$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=INDEX(ACPeriods!$B$3:$B$51,MATCH($C$1,ACPeriods!$A$3:$A$51,0)))*(Invoices!$A$2:$A$9999<=INDEX(ACPeriods!$C$3:$C$51,MATCH($C$1,ACPeriods!$A$3:$A$51,0)))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),""),"")
A5:C16	A5	=IFERROR(INDEX(Invoices!A$2:A$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)-ROW(Invoices!$A$1)/((Invoices!$A$2:$A$9999>=INDEX(ACPeriods!$B$3:$B$51,MATCH($C$1,ACPeriods!$A$3:$A$51,0)))*(Invoices!$A$2:$A$9999<=INDEX(ACPeriods!$C$3:$C$51,MATCH($C$1,ACPeriods!$A$3:$A$51,0)))*(NOT(ISNA(MATCH(Invoices!$B$2:$B$9999,$E$1:$I$1,0))))),ROW()-ROW($A$4))),"")

Cells with Data Validation
Cell	Allow	Criteria
C1	List	=ACPeriods!$A$3:$A$51