Lookup value if between two dates

jeff_miller

New Member
Joined
Apr 7, 2005
Messages
44
Looking for a formula in excel, was thinking of an IF with AND or VLOOKUP??
I have a database in Excel that has
A B C
1 9/1/98 8/31/00 RED
2 9/1/00 8/31/02 BLUE
3 9/1/02 8/31/04 GREEN
4 9/1/04 8/31/06 YELLOW

What formula would I use to take a Date (lets say its located in A8) and look up if it is BETWEEN the dates in column A and B above that it would give the result of column C

Examples
A8 = 11/07/99 would give the result of RED
A8 = 03/02/03 would give the result of GREEN

Thank You!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,924
Office Version
  1. 365
Platform
  1. MacOS
use
=INDEX(C$2:C$5,MATCH(1,IF(G2>=A$2:A$5,IF(G2<=B$2:B$5,1)),0))
and enter using

Control+Shift+Enter


Excel Workbook
ABCDEFGHIJK
1
209/01/199831/08/2000RED11/07/1999RED
309/01/200031/08/2002BLUE03/02/2003GREEN
409/01/200231/08/2004GREEN
509/01/200431/08/2006YELLOW
6
7A8 = 11/07/99 would give the result of RED
8A8 = 03/02/03 would give the result of GREEN
9
Sheet1
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
As long as column A is sorted ascending you could use something like....

=LOOKUP(A8,$A$1:$C$4)
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,924
Office Version
  1. 365
Platform
  1. MacOS
Actually you should be able to use
=LOOKUP(2,1/((G2>=$A$1:$A$6)*(G2<=$B$1:$B$6)),$C$1:$C$6)
that does not require a array entry
so just use ENTER
 

jeff_miller

New Member
Joined
Apr 7, 2005
Messages
44

ADVERTISEMENT

Thank You Weazel that one worked the best! I knew it could be simple just couldn't remember what the formula was.
 

CodenameAter

New Member
Joined
Jul 23, 2014
Messages
22
Hello All,

I was hoping you could expand on the above to take into account another variable. I have a similar problem, I have attached an excel sheet outline the problem.

The only difference is that I am looking at a second by second analysis, returning values of 1, -1 or 0.

What I need added to this is as follows:
-a value of "0" if there is no date/time range available in the chart
-return a value only if the header, in this case MBR, is in the Band column

As of now I get NA for 0 values and it is still returning values for band MLN when in fact it should only return values of MBR.

Below is the charts I am working off of; I put the values I would like to see under MBR and MLN but as I said I can't get it to account for MBR nor produce 0 values. I know I could simply create a separate time on, time off for each band but we could have up to 50 bands and thousands of lines of data. I am sure it is a simple edit of the formula above, I am just completely stumped.

Formula I am currently using, doesn't produce "0" or account for band:
{=INDEX("Bowl Value",MATCH(1,IF("Time">="Time On",IF("Time"<="Time Off",1)),0))}


BandBowl ValueTime OnTime Off
MBR100:0000:03
MBR-100:0500:08
MLN100:0600:09

<tbody>
</tbody>

TimeMBRMLN
00:0010
00:0110
00:0210
00:0310
00:0400
00:05-10
00:06-11
00:07-11
00:08-11
00:0901

<tbody>
</tbody>

I have tried adding IFs and so forth to the above formula with no avail, I am thinking maybe a sumproduct might help but truthfully I am very lost. Let me know. Thank you.
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155

ADVERTISEMENT

maybe....


Excel 2012
ABCD
1BandBowl ValueTime OnTime Off
2MBR10:000:03
3MBR-10:050:08
4MLN10:060:09
5
6TimeMBRMLN
70:0010
80:0110
90:0210
100:0310
110:0400
120:05-10
130:06-11
140:07-11
150:08-11
160:0901

<tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
B7=SUMPRODUCT(($A$2:$A$4=B$6)*($A7>=$C$2:$C$4)*(A7<=$D$2:$D$4),$B$2:$B$4)
C7=SUMPRODUCT(($A$2:$A$4=C$6)*($A7>=$C$2:$C$4)*(B7<=$D$2:$D$4),$B$2:$B$4)

<tbody>
</tbody>

<tbody>
</tbody>
 

CodenameAter

New Member
Joined
Jul 23, 2014
Messages
22
You know those moments when you feel like an idiot, after spending a ridiculous amount of time trying to solve something that had the easy solution; well sir, I am having that moment.

Thank you, this worked like a charm, way easier than anything I was trying to use!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,951
Messages
5,525,842
Members
409,666
Latest member
aquabit

This Week's Hot Topics

Top