match customer and date of service to another spreadsheet that has customer and check-in / check-out date range

[donh]

I have a file that has customer # and date a complaint was made and another log spreadsheet that has customer # and check-in and check-out date.

Not all customers will have filed a complaint in the date of service file.

Is there a way to flag the log file when the customer numbers match and when the complaint date is between the check-in / check-out dates?

I am not having any luck at all

the complaint spreadsheet has:

A	B       C       	D
	cust#	service date	complaint
3	6358535	04/09/2016	COMPLAINT

The customer log file has:

A	B	C       	D               E
	cust#	check in	check out	
3	6358535	02/24/2018	02/27/2018	
4	6358535	04/05/2016	04/20/2016	complaint
5	6358535	05/24/2016	06/03/2016	
6	6358535	06/27/2016	07/17/2016

any help would be much appreciated.

 

[Will85]

Can the same customer number appear multiple times in the complaint spreadsheet? Can there be multiple complaints within a check-in and check-out period for the same customer?

 

[Will85]

=+COUNTIFS('Complaint Spreadhseet'!B:B, 'Customer Log'!B2, 'Complaint Spreadhseet'!C:C, ">="&'Customer Log'!C2, 'Complaint Spreadhseet'!C:C, "<="&'Customer Log'!D2)

this would count the number of complaints within the range on the Customer Log tab, if you dont want the count it can be substituted to a simple binary "Complaint" or blank . . .

 

[donh]

I can make your formula work!

BIG THANKS