Index and Match value with partial text between two dates

[edilly]

I'm building an overview spreadsheet that takes information from multiple different reports. The formula I'm struggling with identifies employees by last name while all the other reports use full name (Last, First, MI), so I need to use partial text or wildcards. The report provides a row for each contract signed yearly by the employee, so I need to locate the current contract for said employee. Finally I want to either display the "Signed" if it's been signed, or leave it blank if it hasn't on the employee overview sheet.

Imported Report Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Last Name[/TD]
[TD]Supervisor[/TD]
[TD]Eff Start[/TD]
[TD]Eff End[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Budge[/TD]
[TD]Grier, Josh[/TD]
[TD]8/1/2017[/TD]
[TD]7/31/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Budge[/TD]
[TD][/TD]
[TD]8/1/2018[/TD]
[TD]7/31/2019[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cardiel[/TD]
[TD]Myers, Ralph[/TD]
[TD]11/1/2017[/TD]
[TD]10/31/2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cardiel[/TD]
[TD]Myers, Ralph[/TD]
[TD]11/1/2018[/TD]
[TD]10/31/2019[/TD]
[/TR]
</tbody>[/TABLE]

Employee Overview Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee[/TD]
[TD]Contract Signed[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Budge, Thomas M.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cardiel, Steven J.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm using named ranges to simply the process when I import the reports. In the end I want B2 on the "Overview Sheet" to stay blank and B3 to fill with "Signed" or something else indicated that his contract is signed.

Here's the formulas that I've tried so far in Sheet2!B3
=INDEX(Sheet1!B2:B5,MATCH(1,IF(AND(Sheet1!C2:C5<=TODAY(),(Sheet1!D2:D5>=TODAY())),"*"&Sheet1!A2:A5&"*"),Sheet2!A2:A3),0)

 

[AhoyNC]

Your formula is not working because the AND function can't return an array and the IF function doesn't support wildcards.

Maybe one of the formulas below will help. If you have Excel 2010 or later then you could use the AGGREGATE function in cell B2, or the array formula in cell C2 will work in any ver., but it must be entered with CTRL-SHIFT-ENTER. Drag formulas down as needed.

Excel Workbook
	A	B	C
1	Employee	Contract Signed	Contract Signed
2	Budge, Thomas M.
3	Cardiel, Steven J.	Signed	Signed
Sheet2

Excel Workbook
	A	B	C	D
1	Last Name	Supervisor	Eff Start	Eff End
2	Budge	Grier, Josh	8/1/2017	7/31/2018
3	Budge		8/1/2018	7/31/2019
4	Cardiel	Myers, Ralph	11/1/2017	10/31/2018
5	Cardiel	Myers, Ralph	11/1/2018	10/31/2019
Sheet1

 

[edilly]

Thanks, that worked! I was running into "excel ran out of resources" errors. Once I cleaned up a few other things on the spreadsheet, its working perfectly.

 

[AhoyNC]

Your welcome.Thanks for the feedback and welcome to the forum.