Index match including a date range with one criteria. For Excel.

[aygreyson]

I'm updating this question to an EXCEL FORMAT. I'm currently trying to pull data from the Workers column into a new sheet via Index Match, printing the workers' names each time they show up in a particular date range.

Workers	Company	Start Date	End Date
Jane Smith	Microsoft	02/03/2022	03/31/2022
Mike Roberts	Google	03/01/2022	06/30/2022
Aaron Smith	Facebook	02/01/2022	04/30/2022
Fred Birch	Microsoft	4/01/2022	4/30/2022

Currently, the equation I have is:

Index(Sheet1!A:A, match(if(Sheet1!C:C<=A2),if('Sheet1D:D>=B2),if('Sheet1!B:B=C2),True,False),False),False)),0),1)

But it seems to only be pulling the first name in the column and not the second name according to company and date range. Any sort of help pointing me in the right direction would be helpful. Thanks.

Start Date	End Date	Company	Workers
01/01/2022	01/31/2022	Microsoft
02/01/2022	02/28/2022	Microsoft
03/01/2022	03/31/2022	Microsoft
04/01/2022	04/30/2022	Microsoft
05/01/2022	05/31/2022	Microsoft

***Edit*** I was unable to delete the original post.

 

[KRice]

Welcome to the MrExcel board. What you are describing is the behavior of the MATCH function. It returns only the first match that it finds. What version of Excel are you using?...Please update your account details with that so that it shows in your profile, as some functions that are available in Excel 365 would make this easier.

 

[KRice]

Here is an example if you have Excel 365:

MrExcel_20220601.xlsx
	A	B	C	D
1	Workers	Company	Start	End
2	Jane Smith	Microsoft	2/3/2022	3/31/2022
3	Mike Roberts	Google	3/1/2022	6/30/2022
4	Aaron Smith	Facebook	2/1/2022	4/30/2022
5	Fred Birch	Microsoft	4/1/2022	4/30/2022
6	Johnny Jones	Microsoft	2/3/2022	3/31/2022
7
8
9
10
11	Start	End	Company	Workers
12	1/1/2022	1/31/2022	Microsoft
13	2/1/2022	2/28/2022	Microsoft
14	3/1/2022	3/31/2022	Microsoft	Jane Smith, Johnny Jones
15	4/1/2022	4/30/2022	Microsoft	Fred Birch
16	5/1/2022	5/31/2022	Microsoft
17	1/1/2022	1/31/2022	Google
18	2/1/2022	2/28/2022	Google
19	3/1/2022	3/31/2022	Google	Mike Roberts
Sheet1

Cell Formulas
Range	Formula
D12:D19	D12	=TEXTJOIN(", ",TRUE,INDEX(FILTER($A$2:$D$6,($C$2:$C$6<=A12)*($D$2:$D$6>=B12)*($B$2:$B$6=C12),""),,1))

 

[aygreyson]

Welcome to the MrExcel board. What you are describing is the behavior of the MATCH function. It returns only the first match that it finds. What version of Excel are you using?...Please update your account details with that so that it shows in your profile, as some functions that are available in Excel 365 would make this easier.

Hi, I'm currently using Excel 16.61.1 (2019)

 

[aygreyson]

Here is an example if you have Excel 365:

MrExcel_20220601.xlsx
	A	B	C	D
1	Workers	Company	Start	End
2	Jane Smith	Microsoft	2/3/2022	3/31/2022
3	Mike Roberts	Google	3/1/2022	6/30/2022
4	Aaron Smith	Facebook	2/1/2022	4/30/2022
5	Fred Birch	Microsoft	4/1/2022	4/30/2022
6	Johnny Jones	Microsoft	2/3/2022	3/31/2022
7
8
9
10
11	Start	End	Company	Workers
12	1/1/2022	1/31/2022	Microsoft
13	2/1/2022	2/28/2022	Microsoft
14	3/1/2022	3/31/2022	Microsoft	Jane Smith, Johnny Jones
15	4/1/2022	4/30/2022	Microsoft	Fred Birch
16	5/1/2022	5/31/2022	Microsoft
17	1/1/2022	1/31/2022	Google
18	2/1/2022	2/28/2022	Google
19	3/1/2022	3/31/2022	Google	Mike Roberts
Sheet1

Cell Formulas
Range	Formula
D12:D19	D12	=TEXTJOIN(", ",TRUE,INDEX(FILTER($A$2:$D$6,($C$2:$C$6<=A12)*($D$2:$D$6>=B12)*($B$2:$B$6=C12),""),,1))

Also, I just tried this equation and an error appears stating "This function is not Valid" I think this is due to the text being read as numerals, but I still need help figuring out what direction to head in. Thanks.

 

[KRice]

No, the error is because the formula I used relies on functions found in Excel 365. Excel 2019 will require a different approach.

 

[KRice]

In Excel 2019, something like this should work. The FILTER function is not available, so the matching Workers need to be aggregated somehow. That is done here using an INDEX/AGGREGATE construction. The formula will determine how many matches exist, and then the formula needs to be selected and dragged across the results table so that it (the formula) can increment which particular match to return...and the formula should be dragged to the right until no more matches appear. This returns only one match per cell...and if you want all of the results assembled into a single cell, that can be done using a TEXTJOIN function as shown.

MrExcel_20220601.xlsx
	A	B	C	D	E	F	G	H	I	J
10				Excel 365		Excel 2019 -->				Concatenated
11	Start	End	Company	Workers		Workers
12	1/1/2022	1/31/2022	Microsoft
13	2/1/2022	2/28/2022	Microsoft
14	3/1/2022	3/31/2022	Microsoft	Jane Smith, Johnny Jones		Jane Smith	Johnny Jones			Jane Smith, Johnny Jones
15	4/1/2022	4/30/2022	Microsoft	Fred Birch		Fred Birch				Fred Birch
16	5/1/2022	5/31/2022	Microsoft
17	1/1/2022	1/31/2022	Google
18	2/1/2022	2/28/2022	Google
19	3/1/2022	3/31/2022	Google	Mike Roberts		Mike Roberts				Mike Roberts
Sheet1

Cell Formulas
Range	Formula
F12:I19	F12	=IFERROR(IF(SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12))>0, INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)), IF(COLUMNS($F:F)<=SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)),COLUMNS($F:F)))),""),"")
J12:J19	J12	=TEXTJOIN(", ",TRUE,F12:I12)
D12:D19	D12	=TEXTJOIN(", ",TRUE,INDEX(FILTER($A$2:$D$6,($C$2:$C$6<=A12)*($D$2:$D$6>=B12)*($B$2:$B$6=C12),""),,1))

 

[aygreyson]

In Excel 2019, something like this should work. The FILTER function is not available, so the matching Workers need to be aggregated somehow. That is done here using an INDEX/AGGREGATE construction. The formula will determine how many matches exist, and then the formula needs to be selected and dragged across the results table so that it (the formula) can increment which particular match to return...and the formula should be dragged to the right until no more matches appear. This returns only one match per cell...and if you want all of the results assembled into a single cell, that can be done using a TEXTJOIN function as shown.

MrExcel_20220601.xlsx
	A	B	C	D	E	F	G	H	I	J
10				Excel 365		Excel 2019 -->				Concatenated
11	Start	End	Company	Workers		Workers
12	1/1/2022	1/31/2022	Microsoft
13	2/1/2022	2/28/2022	Microsoft
14	3/1/2022	3/31/2022	Microsoft	Jane Smith, Johnny Jones		Jane Smith	Johnny Jones			Jane Smith, Johnny Jones
15	4/1/2022	4/30/2022	Microsoft	Fred Birch		Fred Birch				Fred Birch
16	5/1/2022	5/31/2022	Microsoft
17	1/1/2022	1/31/2022	Google
18	2/1/2022	2/28/2022	Google
19	3/1/2022	3/31/2022	Google	Mike Roberts		Mike Roberts				Mike Roberts
Sheet1

Cell Formulas
Range	Formula
F12:I19	F12	=IFERROR(IF(SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12))>0, INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)), IF(COLUMNS($F:F)<=SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)),COLUMNS($F:F)))),""),"")
J12:J19	J12	=TEXTJOIN(", ",TRUE,F12:I12)
D12:D19	D12	=TEXTJOIN(", ",TRUE,INDEX(FILTER($A$2:$D$6,($C$2:$C$6<=A12)*($D$2:$D$6>=B12)*($B$2:$B$6=C12),""),,1))

Hi, thank you so much for this formula.

However, I don't quite understand this part "AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)" what do the 15 and 6 positions point to exactly? Thanks.

Or perhaps, I should send a copy of the sheet for clarity purposes.

 

[KRice]

The full AGGREGATE function in this case looks like:

AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)),
IF(COLUMNS($F:F)<=SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)),COLUMNS($F:F)))

AGGREGATE is an older function that takes 4 arguments, which are broken out on separate rows below:

AGGREGATE(
 15,
   6,
    (ROW($A$2:$A$6)-ROW($A$2)+1) / (($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)),
      IF(COLUMNS($F:F)<=SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)),COLUMNS($F:F))
)

1. Most often when you see this function, it is used to return some smaller or larger value in an array (a set of numbers that is the 3rd argument). The 15 means to report the smaller values, while a code of 14 would return larger values.
2. The 6 means to ignore any errors if they exist in the array, which is handy because some constructions of the array (like the one in this example) are designed to intentionally create errors as a way of ignoring certain data.
3. We need some strategy for determining which row in the source data table contains the Name(s) corresponding to the selection criteria (Start and End Dates and Company). So we construct two arrays that can be used to determine which rows in the source table match the criteria.
   • Since the source data are found in rows 2:6, we can use the actual worksheet row numbers ROW($A$2:$A$6) to return an array of {2;3;4;5;6}, but if we want to refer to the first row in the source table to perhaps extract Jane Smith, we need this array to begin with 1. So the upper row number (given by ROW($A$2)) is subtracted from the array, which gives {0;1;2;3;4}, and then we add a 1 to obtain {1;2;3;4;5}. There are various ways to construct this row-indexing array, but I find this one to be fairly intuitive because it directly references parts of the source table to ensure that the entire data section is covered by the row-indexing array. There is nothing sacred about using ROW($A$2:$A$6). The only important thing is that the sequence 2 through 6 is generated...so ROW(A2:ZZ6), ROW(2:6), ROW(F2:F6), would all work fine, but they may be confusing. One could skip this construction altogether and manually replace the row-indexing array with {1;2;3;4;5}, but as more data are added to the source table, this array in the formula would need to be revised. It's easier to inspect the ROW construction to see that it covers the data source.
   • Then another array that considers the matching criteria is constructed. Most of this part is probably obvious:

     ($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12))

     We want to examine the Start Date column in the source data to determine which ones are less than or equal to the Start Date in the results table...so ($C$2:$C$6<=$A12) will produce a five element vertical array of TRUE's and FALSE's, depending on whether the condition is met. Similar expressions are created for the other two criteria. Then since all of these criteria must be met, these three arrays are multiplied together, and in doing so, TRUE's and FALSE's are coerced to 1's and 0's respectively, and the resulting vertical array will have five elements that are either a 1 or 0, with the 1's representing the positions in the source table where valid match(es) to the criteria is(are) found.
   • Finally, we divide the 1st array by the 2nd array. If we take the 3rd row of the results table (3/1/2022, 3/31/2022, Microsoft) as an example, this 3rd argument array would look like {1;2;3;4;5}/{1;0;0;0;1} where we have the row indexes in the numerator array and the logical criteria matches in the denominator array. When the division occurs, it is performed on elements paired by their position, so {1/1; 2/0; 3/0; 4/0; 5/1}, which yields the 3rd argument array: {1;#DIV/0!;#DIV/0!;#DIV/0!;5}. Notice that the division-by-0 entries produce errors by design. The "6" argument mentioned above says to ignore these, so we can see that we are interested in row indexes 1 and 5 where Jane Smith and Johnny Jones are located (but we haven't extracted those names yet...only identified their row indexes). We can also see in this array that we have two matches...something that will be useful and described further below.
4. The 4th argument of the AGGREGATE function is sometimes designated "k" and it describes which specific element in this array to return...the 15 (1st argument) say to return the smaller values, but do we want the 1st smallest, 2nd smallest, 3rd smallest, etc.? This part is a little tricky, but if there are multiple matches, the 1st is to be placed in column F, the 2nd in column G, and so on...so the construction COLUMNS($F:F), where $F is a fixed column location and F (without the $ sign) is a relative location will initially return a value of 1. If this formula is pulled into column G, it would look like COLUMNS($F:G), and since this range covers two columns, a value of 2 would be returned. So we can use the COLUMNS idea as a way to indicate whether AGGREGATE should report the 1st smallest row index number or the 2nd smallest, etc. But we do not want this COLUMNS idea to return nonsense values, such as to return the 3rd smallest row index number if there are only 2 matches, so we count the number of matches using:

   SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12))

   This uses the same construction described for the denominator of the 3rd argument, but this time we simply add the 0's and 1's to determine that there are two matches. The final implementation then says to drag the formula from column F across columns to the right until no more results are returned, and by doing so, the 4th argument in AGGREGATE, given by:

   IF(COLUMNS($F:F)<=SUM(($C$2:$C$6<=$A12)*($D$2:$D$6>=$B12)*($B$2:$B$6=$C12)),COLUMNS($F:F))

   ...performs a conditional test that determines whether the number of columns for reporting results is less than or equal to the total number of results to report, and if so, then the number of columns from the initial starting point is returned. And if this expression evaluates to FALSE, a FALSE will be returned.

The row index (or a FALSE) is then passed to the INDEX function, where the function returns the name in $A$2:$A$6 corresponding to the row index...and if a FALSE is passed to INDEX, an error is generated, which is trapped with an IFERROR function wrapping everything.

 

[aygreyson]

Hi, thank you so much for this explanation. I've read over the previous post twice and implemented it to my sheet. However, my columns are now coming up entirely blank and I'm not sure why. I've attached a sheet showing this equation, and highlighting the fields I've used in green. "The GoalsDashboard" in Column I:I is where I've placed the equation. Outlook Excel