Help with formula if value is greater than X AND less than Y

[Sean15]

Hi:

I have date in C1 - 9/30/18.

Range - A2:A100 has dates

Range - B2:B100 has numbers

I want to return value in B2 if date in A2 is greater than 91 days from 9/30/18 AND less than 180 days from 9/30/18.

Could you help please?

Thank you very much.

Sean

 

[Marcelo Branco]

Maybe...

Array fiormula
=INDEX(B2:B100,MATCH(1,(A2:A100>DATE(2018,9,30)+91)*(DATE(2018,9,30)+180>A2:A100),0))
Ctrl+Shift+Enter

M.

 

[MARK858]

Maybe...

=IF(AND(A2>C1+91,A2<C1+180),B2,"")

copied down (interpreting the question differently to Marcelo)

 

[Fluff]

How about
=IF(AND(A2 >C$1+91,A2< C$1+180),B2,"")

I'm obviously way too slow typing

 

[MARK858]

I'm obviously way too slow typing

Not really as I forgot to hard code C1, it should have been...

=IF(AND(A2>$C$1+91,A2<$C$1+180),B2,"")

in post number 3

 

[Sean15]

Thank you very much.


Regards,

Sean