# Lookup Between Date Range

#### Nick van Staden

##### New Member
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​

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Special-K99

##### Well-known Member
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

##### New Member
Thank you, highly appreciated.

You are 100% correct. works perfect.

Replies
1
Views
66
Replies
9
Views
119
Replies
8
Views
75
Replies
4
Views
65
Replies
18
Views
187

1,112,860
Messages
5,542,935
Members
410,577
Latest member
ZvK