Index/Match Formula

[btadams]

Hello everybody,

I'm trying to put together an Index/Match formula. I have two tabs and both have a column with user id's. The second tab also has a ProposedStartDate column and DateClosed column. In the first tab I want a formula that will look up the user id and return 0 or 1 if that user has a ProposedStartDate or a DateClosed between 1/1/2018 and 12/31/2018.

I've tried the formulas below with no luck:

=INDEX(USERID,MATCH(1,(ProposedStartDate>=$C$1)*(ProposedStartDate<=$D$1)*(A2=USERID),0)) (ctrl+shift+enter)

=SUMPRODUCT((USERID=A2)*(ProposedStartDate>=$C$1)*(ProposedStartDate<=$D$1))

where 1/1/2018 and 12/31/2018 are in cells $C$1 and $D$1.

Thanks,

Brian

 

[RasGhul]

Could you post some sample data?

 

[AhoyNC]

Your SUMPRODUCT should work. Maybe check your named ranges.

Excel Workbook
	A	B	C	D
1	UserID		1/1/2018	12/31/2018
2	ID2	1
3	ID3	0
4	ID4	1
5	ID6	1
6	ID15	0
sheet1

#VALUE!

</td></tr></table></td></tr></table>

Excel Workbook
	A	B	C
1	UserID	ProposedStartDate	DateClosed
2	ID2	2/5/2018	4/6/2018
3	ID3	12/12/2018	1/5/2019
4	ID4	6/6/2018	7/4/2018
5	ID15	2/14/2019	3/6/2019
6	ID6	5/12/2018	9/10/2018
sheet2

 

[btadams]

AhoyNC,

Yes, it was a problem with the named ranges.

Thanks!