Excel help Index match on Date across 2 tables

Amex123

New Member
Joined
Jan 5, 2016
Messages
18
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)StaffComm_Fact
01/05/2021Adam_H2%
01/07/2021Sue_H5%
03/05/2021Adam_H3%
05/01/2021Adam_H3.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
MonthStaffSalesExpected Commission
Jan 2021Adam_H100020
Feb 2021Adam_H100020
Mar 2021Adam_H100030
April 2021Adam_H100030
May 2021Adam_H100035
June 2021Adam_H100035

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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,348
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 5, 2016
Messages
18
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,348
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 5, 2016
Messages
18

ADVERTISEMENT

Index_match_Dates.xlsx
ABCDEFGHI
1Table1Table2notes
2Date (mm/dd/yyyy)StaffComm_FactMonthStaff Sales Commission{=INDEX($C:$C,MATCH(1,($B:$B=F2)*(A:A>=E2),0))*G2}
31/5/2021Adam_H2%Jan-21Adam_H$ 10,000.00$ 200.00= Sales * Comm_fact Jan (2%)
41/7/2021Sue_H5%Feb-21Adam_H$ 10,000.00$ 300.00= Sales * Comm_fact Jan (2%)
53/5/2021Adam_H3%Mar-21Adam_H$ 10,000.00$ 300.00= Sales * Comm_fact March (3%)
65/1/2021Adam_H4%Apr-21Adam_H$ 10,000.00$ 350.00= Sales * Comm_fact March (3%)
7May-21Adam_H$ 10,000.00$ 350.00= Sales * Comm_fact April (3.5%)
8Jun-21Adam_H$ 10,000.00#N/A= Sales * Comm_fact April (3.5%)
9Jul-21Adam_H$ 10,000.00#N/A= Sales * Comm_fact April (3.5%)
10Aug-21Adam_H$ 10,000.00#N/A= Sales * Comm_fact April (3.5%)
11Sep-21
12Oct-21
13Nov-21
14Dec-21
Sheet1
Cell Formulas
RangeFormula
H3:H10H3=INDEX($C:$C,MATCH(1,($B:$B=F3)*(A:A>=E3),0))*G3
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Amex123

New Member
Joined
Jan 5, 2016
Messages
18
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
ABCDEFGHI
1Table1Table2notes
2Date (mm/dd/yyyy)StaffComm_FactMonthStaff Sales Commission{=INDEX($C:$C,MATCH(1,($B:$B=F2)*(A:A>=E2),0))*G2}
31/5/2021Adam_H2%Jan-21Adam_H$ 10,000.00$ 200.00= Sales * Comm_fact Jan (2%)
41/7/2021Sue_H5%Feb-21Adam_H$ 10,000.00$ 300.00= Sales * Comm_fact Jan (2%)
53/5/2021Adam_H3%Mar-21Adam_H$ 10,000.00$ 300.00= Sales * Comm_fact March (3%)
65/1/2021Adam_H4%Apr-21Adam_H$ 10,000.00$ 350.00= Sales * Comm_fact March (3%)
7May-21Adam_H$ 10,000.00$ 350.00= Sales * Comm_fact April (3.5%)
8Jun-21Adam_H$ 10,000.00#N/A= Sales * Comm_fact April (3.5%)
9Jul-21Adam_H$ 10,000.00#N/A= Sales * Comm_fact April (3.5%)
10Aug-21Adam_H$ 10,000.00#N/A= Sales * Comm_fact April (3.5%)
11Sep-21
12Oct-21
13Nov-21
14Dec-21
Sheet1
Cell Formulas
RangeFormula
H3:H10H3=INDEX($C:$C,MATCH(1,($B:$B=F3)*(A:A>=E3),0))*G3
Press CTRL+SHIFT+ENTER to enter array formulas.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,348
Office Version
  1. 365
Platform
  1. Windows
Using Power Query and three queries.

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

Power Query:
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

Power Query:
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.

Power Query:
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
EFGH
12DateStaff Sales Multiplication
13January-2021Adam_H10000200
14February-2021Adam_H10000200
15March-2021Adam_H10000300
16April-2021Adam_H10000300
17May-2021Adam_H10000350
18June-2020Adam_H10000350
19July-2021Adam_H10000350
20August-2021Adam_H10000350
Sheet4
 

Forum statistics

Threads
1,136,445
Messages
5,675,899
Members
419,591
Latest member
mersanko

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
Top