Excel help Index match on Date across 2 tables

[Amex123]

Hi needed help with Index match on Date:

I have a table tab1 with Staff members and dates their commission factor is adjusted

Date (mm/dd/yyyy)	Staff	Comm_Fact
01/05/2021	Adam_H	2%
01/07/2021	Sue_H	5%
03/05/2021	Adam_H	3%
05/01/2021	Adam_H	3.5%

i have another table, tab2 for each month of the year where i need to use this efficiency factor to calculate commission based on sales

Month	Staff	Sales	Expected Commission
Jan 2021	Adam_H	1000	20
Feb 2021	Adam_H	1000	20
Mar 2021	Adam_H	1000	30
April 2021	Adam_H	1000	30
May 2021	Adam_H	1000	35
June 2021	Adam_H	1000	35

need a formula to use the factor for the month and till the month it changes and the use the new factor:
tried something like this : =Index tab1C:C,(match(1,(tab1B:B="Adam_H")*( tab1A:A<=Tab2A2 ),0))

need help with the date match portion

 

[alansidman]

Suggest you reload your data using XL2BB as it is difficult to determine from a picture if your dates are text or real dates especially since Tab2 Dates are showing as month and year and tab 1 is showing what appears to be date format. With XL2BB we can manipulate the data efficiently and will not have to guess at the data types.

 

[Amex123]

Suggest you reload your data using XL2BB as it is difficult to determine from a picture if your dates are text or real dates especially since Tab2 Dates are showing as month and year and tab 1 is showing what appears to be date format. With XL2BB we can manipulate the data efficiently and will not have to guess at the data types.

Thanks for the suggestion both the dates are in the date format - they just like booking them that way - in the input sheet its a specific date in the output its 1st of every month

 

[alansidman]

The issue is for me to help you, I need to recreate your information. I am lazy. Help me to help you by providing a sample that I can manipulate. Time is valuable and I don't wish to spend my time recreating your already created file. If you wish my assistance, then you will honor my request, otherwise, you can hope that someone else will be interested.

 

[Amex123]

Index_match_Dates.xlsx
	A	B	C	D	E	F	G	H	I
1		Table1				Table2			notes
2	Date (mm/dd/yyyy)	Staff	Comm_Fact		Month	Staff	Sales	Commission	{=INDEX($C:$C,MATCH(1,($B:$B=F2)*(A:A>=E2),0))*G2}
3	1/5/2021	Adam_H	2%		Jan-21	Adam_H	$ 10,000.00	$ 200.00	= Sales * Comm_fact Jan (2%)
4	1/7/2021	Sue_H	5%		Feb-21	Adam_H	$ 10,000.00	$ 300.00	= Sales * Comm_fact Jan (2%)
5	3/5/2021	Adam_H	3%		Mar-21	Adam_H	$ 10,000.00	$ 300.00	= Sales * Comm_fact March (3%)
6	5/1/2021	Adam_H	4%		Apr-21	Adam_H	$ 10,000.00	$ 350.00	= Sales * Comm_fact March (3%)
7					May-21	Adam_H	$ 10,000.00	$ 350.00	= Sales * Comm_fact April (3.5%)
8					Jun-21	Adam_H	$ 10,000.00	#N/A	= Sales * Comm_fact April (3.5%)
9					Jul-21	Adam_H	$ 10,000.00	#N/A	= Sales * Comm_fact April (3.5%)
10					Aug-21	Adam_H	$ 10,000.00	#N/A	= Sales * Comm_fact April (3.5%)
11					Sep-21
12					Oct-21
13					Nov-21
14					Dec-21
Sheet1

Cell Formulas
Range	Formula
H3:H10	H3	=INDEX($C:$C,MATCH(1,($B:$B=F3)*(A:A>=E3),0))*G3
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Amex123]

The issue is for me to help you, I need to recreate your information. I am lazy. Help me to help you by providing a sample that I can manipulate. Time is valuable and I don't wish to spend my time recreating your already created file. If you wish my assistance, then you will honor my request, otherwise, you can hope that someone else will be interested.

Index_match_Dates.xlsx
	A	B	C	D	E	F	G	H	I
1		Table1				Table2			notes
2	Date (mm/dd/yyyy)	Staff	Comm_Fact		Month	Staff	Sales	Commission	{=INDEX($C:$C,MATCH(1,($B:$B=F2)*(A:A>=E2),0))*G2}
3	1/5/2021	Adam_H	2%		Jan-21	Adam_H	$ 10,000.00	$ 200.00	= Sales * Comm_fact Jan (2%)
4	1/7/2021	Sue_H	5%		Feb-21	Adam_H	$ 10,000.00	$ 300.00	= Sales * Comm_fact Jan (2%)
5	3/5/2021	Adam_H	3%		Mar-21	Adam_H	$ 10,000.00	$ 300.00	= Sales * Comm_fact March (3%)
6	5/1/2021	Adam_H	4%		Apr-21	Adam_H	$ 10,000.00	$ 350.00	= Sales * Comm_fact March (3%)
7					May-21	Adam_H	$ 10,000.00	$ 350.00	= Sales * Comm_fact April (3.5%)
8					Jun-21	Adam_H	$ 10,000.00	#N/A	= Sales * Comm_fact April (3.5%)
9					Jul-21	Adam_H	$ 10,000.00	#N/A	= Sales * Comm_fact April (3.5%)
10					Aug-21	Adam_H	$ 10,000.00	#N/A	= Sales * Comm_fact April (3.5%)
11					Sep-21
12					Oct-21
13					Nov-21
14					Dec-21
Sheet1

Cell Formulas
Range	Formula
H3:H10	H3	=INDEX($C:$C,MATCH(1,($B:$B=F3)*(A:A>=E3),0))*G3
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alansidman]

Using Power Query and three queries.

First Query is for table 1 to manipulate date to month and year only

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date (mm/dd/yyyy)", type date}, {"Staff", type text}, {"Comm_Fact", type number}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([#"Date (mm/dd/yyyy)"]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([#"Date (mm/dd/yyyy)"]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Date (mm/dd/yyyy)"})
in
    #"Removed Columns"

Second Query is for table 2 to change date to Month and Year Only

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type datetime}, {"Staff", type text}, {" Sales ", Int64.Type}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Month]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Month]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Month"})
in
    #"Removed Columns"

Third query is to merge first two and calculate commission.

let
    Source = Table.NestedJoin(Table2, {"Staff", "Date"}, Table1, {"Staff", "Date"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Comm_Fact"}, {"Table1.Comm_Fact"}),
    #"Filled Down" = Table.FillDown(#"Expanded Table1",{"Table1.Comm_Fact"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Filled Down", "Multiplication", each [#" Sales "] * [Table1.Comm_Fact], type number),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Multiplication",{"Date", "Staff", " Sales ", "Table1.Comm_Fact", "Multiplication"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Table1.Comm_Fact"})
in
    #"Removed Columns"

Result:

Sample.xlsx
	E	F	G	H
12	Date	Staff	Sales	Multiplication
13	January-2021	Adam_H	10000	200
14	February-2021	Adam_H	10000	200
15	March-2021	Adam_H	10000	300
16	April-2021	Adam_H	10000	300
17	May-2021	Adam_H	10000	350
18	June-2020	Adam_H	10000	350
19	July-2021	Adam_H	10000	350
20	August-2021	Adam_H	10000	350
Sheet4