Return text value based on matching criteria including date range

[trancer78]

Hi Everybody,

I am trying to get a formula to return a text value based on a couple inputs. Where I am running into trouble is that one of the inputs wouldn't be a match, but would be a date between a start/end date within the range it is looking at.

Does anybody have any ideas on how I could return the value of column A when H2 matches column C and the date in H3 if between the dates in columns D & E?

	A	B	C	D	E	F	G	H
1	Project Name	Status	Project Code	Start Date	End Date		INPUTS
2	Conversion Code Report	Closed	4	01/24/18	01/26/18		Project Code:	3
3	Pre-Cutover	Closed	3	01/08/18	02/06/18		Date:	12/21/2017
4	Adr/Occ reporting	Closed	3	12/20/17	12/28/17
5	Password Manager	Closed	5	12/14/17	12/28/17		Result:	Adr/Occ reporting
6	Resyncs	Closed	3	12/05/17	12/07/15

<tbody>
</tbody>

 

[Special-K99]

Your data is wrong in D6 and E6
E6 cant be less than D6

 

[Special-K99]

=INDEX(A2:A6,MATCH(H2,IF((H3>=D2:D6)*(H3<=E2:EF6),C2:C6),0),1)
Array formula, use Ctrl-Shift-Enter

 

[trancer78]

Your data is wrong in E5 and E6
E6 cant be less than E5

I'm not sure what you mean. The end dates in E5 and E6 are 2 different projects.

 

[Special-K99]

Sorry D6 and E6 that should be

 

[trancer78]

Ahhh... yes, that end date was a typo.
Thanks for the help with the formula... worked great!

 

[Special-K99]

Here's an alternative that doesn't use an array formula (which slows things down)


=IFERROR(LOOKUP(2,1/(D2:D6=I2)/(I3>=E2:E6)/(I3<=F2:F6),B2:B6),"")

 

[trancer78]

Even better, thanks again!