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]

You actually didn't need the explanation. I thought you were using Excel 2019, but it appears that you are using an online version with most (all?) of the Excel 365 features...so you can use the much simpler version I posted initially. But your dataset has issues. You have errors in the Active sheet beginning on row 158 and that causes the formula to fail. Those errors should be trapped to produce a blank. For now, I set the range of the arrays that are evaluated for rows 2:157 on the Active sheet, which doesn't quite cover the entire Active data table, but it allows the formula to work and obtain names from those rows on the Active sheet. Once the errors are fixed, the ranges in the formula can be expanded. Similarly, on the Dashboard sheet, there is something that prevents the formula from automatically copying down the entire length of the I column...I'm not sure why. I performed a manual copy/paste to populate the cells.

 

[aygreyson]

You actually didn't need the explanation. I thought you were using Excel 2019, but it appears that you are using an online version with most (all?) of the Excel 365 features...so you can use the much simpler version I posted initially. But your dataset has issues. You have errors in the Active sheet beginning on row 158 and that causes the formula to fail. Those errors should be trapped to produce a blank. For now, I set the range of the arrays that are evaluated for rows 2:157 on the Active sheet, which doesn't quite cover the entire Active data table, but it allows the formula to work and obtain names from those rows on the Active sheet. Once the errors are fixed, the ranges in the formula can be expanded. Similarly, on the Dashboard sheet, there is something that prevents the formula from automatically copying down the entire length of the I column...I'm not sure why. I performed a manual copy/paste to populate the cells.

I am using Excel 2019 but shared with Outlook for ease's sake, but I see you've updated the file. Many thanks, I'm now working on converting this back to Excel 2019.

 

[KRice]

Well that will not work then and you will indeed need a different formula, such as the INDEX/AGGREGATE one I posted. But it will require using multiple columns to list out matching names before they can be consolidated into your desired target cell. Please examine the revised file you posted to confirm that the results are as expected. The Excel 365 version of the formula is in column O while the Excel 2019 formula is in helper columns P:T (assuming you will have no more than 5 names for any given row?). Then the names on any given row from the five helper columns are consolidated into a single cell in column U. If used on Excel 2019, you will need to leave the helper columns somewhere out of the way or hidden, and then bring the formula shown in column U into your main results table.

For the formulas that are producing errors on the Active sheet, those errors will also produce errors when these formulas attempt to examine those columns...so the errors need to be fixed or trapped. This is why I found it necessary to use a smaller range in the formulas I added, otherwise none of them would work. I believe most of them are caused by INDEX/MATCH functions that do not find any matches. The easiest remedy is to wrap the formula with an IFERROR. For example, instead of the 1st use the 2nd:

=INDEX(B5:B39736, MATCH(P5, C5:C39736, 0)
=IFERROR(INDEX(B5:B39736, MATCH(P5, C5:C39736, 0),"")

The blanks, instead of the error messages, should allow subsequent references to those cells to work okay. Once the errors are addressed, you can revise the formulas and extend the range to cover the full length of your data table.
Three things about your table:
1) Is the range shown in the formula example above really necessary? 39736 is very long...If you can specify a range that is long enough, but not too long, then I would try that. Once you do that, there should be no problem rounding that number up to an easier to remember--and easier to inspect--number. In this case, I would just use 40000 everywhere.
2) I see those INDEX/MATCH functions entered as array formulas. They should not need to be entered like that. Try editing the formula and simply entering it and copying it down the column to confirm that it works okay as a regular formula.
3) Those INDEX/MATCH functions are probably not going to provide the information you want because none of the ranges are "fixed" with $ signs. I suspect you want something like:

=IFERROR(INDEX($B$5:$B$39736, MATCH($P5, $C$5:$C$39736, 0),"")

...where the ranges used by INDEX and the MATCH lookup array are "fixed", while the lookup value ($P5 here) is the only value that changes. I found the web interface to this file very "clunky" to work with, and any changes I attempted had significant lag times, so I did not investigate this further, but on your regular version of the file, please have a look at all of the INDEX/MATCH functions, as I believe they are not constructed quite right.