Lookup Between Date Range

Nick van Staden

New Member
Joined
Dec 6, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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.

REFACTION_DATERESULTSTARDENDREF
Feb-19
Mar-19
Apr-19
May-19
Jun-19
1​
2019/02/25​
Successful
2019/01/01​
2019/01/31​
1​
SuccessfulSuccessfulUnsuccessfulSuccessfulUnsuccessful
1​
2019/03/25​
Successful
2019/02/01​
2019/02/28​
2​
N/AUnsuccessfulN/AN/AN/A
1​
2019/04/25​
Unsuccessful
2019/03/01​
2019/03/31​
3​
N/AN/AN/ASuccessfulSuccessful
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​
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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
 

Watch MrExcel Video

Forum statistics

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