Lookup Between Date Range

[Nick van Staden]

Hi Awesome People,

I'm having difficulty tuning a lookup function to return a cell based on a date range as seen in example below:

What I am trying to archive is the value in column 'C' if column 'B' falls within the start and end date of E:F. If it doesnt fall within this date range must return "N/A" (for example).

The formula I am using is: =LOOKUP(2,1/(B3>=E4)*(B3<=F4),C3)

The issue with the formula above is its giving the result (what's in column "C") regardless if it falls within the date parameters or not.

Column "I":"M" is where I want to post the formula (results are examples of what must display.

REF

ACTION_DATE

RESULT

STARD

END

REF

Feb-19​

Mar-19​

Apr-19​

May-19​

Jun-19​

1​

2019/02/25​

Successful

2019/01/01​

2019/01/31​

1​

Successful

Successful

Unsuccessful

Successful

Unsuccessful

1​

2019/03/25​

Successful

2019/02/01​

2019/02/28​

2​

N/A

Unsuccessful

N/A

N/A

N/A

1​

2019/04/25​

Unsuccessful

2019/03/01​

2019/03/31​

3​

N/A

N/A

N/A

Successful

Successful

1​

2019/05/25​

Successful

2019/04/01​

2019/04/30​

1​

2019/06/25​

Unsuccessful

2019/05/01​

2019/05/31​

=LOOKUP(2,1/(B3>=E4)*(B3<=F4),C3)

2​

2019/03/25​

Unsuccessful

2019/06/01​

2019/06/30​

3​

2019/05/25​

Successful

2019/07/01​

2019/07/31​

3​

2019/06/25​

Successful

2019/08/01​

2019/08/31​

2019/09/01​

2019/09/30​

2019/10/01​

2019/10/31​

2019/11/01​

2019/11/30​

2019/12/01​

2019/12/31​

 

[Special-K99]

Shouldn't it be - note extra brackets

=LOOKUP(2,1/((B3>=E4)*(B3<=F4)),C3)

So the conditions get executed first
((B3>=E4)*(B3<=F4))
Then the result gets passed to the 1/ part of the formula

I know you're thining hang on, division and multiplication, order doesn';t matter
But in this case I think it does as there's a "logic" function going on here

 

[Nick van Staden]

Thank you, highly appreciated.

You are 100% correct. works perfect.