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?


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

 

[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!