# Lookup value if between two dates

#### jeff_miller

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

### 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
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
As long as column A is sorted ascending you could use something like....

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

#### etaf

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

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
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))}

 Band Bowl Value Time On Time Off MBR 1 00:00 00:03 MBR -1 00:05 00:08 MLN 1 00:06 00:09

<tbody>
</tbody>

 Time MBR MLN 00:00 1 0 00:01 1 0 00:02 1 0 00:03 1 0 00:04 0 0 00:05 -1 0 00:06 -1 1 00:07 -1 1 00:08 -1 1 00:09 0 1

<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

maybe....

Excel 2012
 A B C D 1 Band Bowl Value Time On Time Off 2 MBR 1 0:00 0:03 3 MBR -1 0:05 0:08 4 MLN 1 0:06 0:09 5 6 Time MBR MLN 7 0:00 1 0 8 0:01 1 0 9 0:02 1 0 10 0:03 1 0 11 0:04 0 0 12 0:05 -1 0 13 0:06 -1 1 14 0:07 -1 1 15 0:08 -1 1 16 0:09 0 1

<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
I'll give it a try! Thanks for the suggestion

#### CodenameAter

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

Replies
2
Views
52
Replies
3
Views
61
Replies
17
Views
129
Replies
4
Views
72
Replies
5
Views
49