Lookup value if between two dates

[jeff_miller]

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!

 

[etaf]

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
	A	B	C	D	E	F	G	H	I	J	K
1
2	09/01/1998	31/08/2000	RED				11/07/1999		RED
3	09/01/2000	31/08/2002	BLUE				03/02/2003		GREEN
4	09/01/2002	31/08/2004	GREEN
5	09/01/2004	31/08/2006	YELLOW
6
7					A8 = 11/07/99 would give the result of RED
8					A8 = 03/02/03 would give the result of GREEN
9
Sheet1

 

[Weazel]

As long as column A is sorted ascending you could use something like....

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

 

[etaf]

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]

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

 

[CodenameAter]

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


[TABLE="width: 500"]
<tbody>[TR]
[TD]Band[/TD]
[TD]Bowl Value[/TD]
[TD]Time On[/TD]
[TD]Time Off[/TD]
[/TR]
[TR]
[TD]MBR[/TD]
[TD]1[/TD]
[TD]00:00[/TD]
[TD]00:03[/TD]
[/TR]
[TR]
[TD]MBR[/TD]
[TD]-1[/TD]
[TD]00:05[/TD]
[TD]00:08[/TD]
[/TR]
[TR]
[TD]MLN[/TD]
[TD]1[/TD]
[TD]00:06[/TD]
[TD]00:09[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]MBR[/TD]
[TD]MLN[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]00:01[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]00:02[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]00:03[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]00:04[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]00:05[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]00:06[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]00:07[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]00:08[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]00:09[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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]

maybe....


Excel 2012[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Band[/TD]
[TD="bgcolor: #FFFF00"]Bowl Value[/TD]
[TD="bgcolor: #FFFF00"]Time On[/TD]
[TD="bgcolor: #FFFF00"]Time Off[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]MBR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:03[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]MBR[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0:05[/TD]
[TD="align: right"]0:08[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]MLN[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0:06[/TD]
[TD="align: right"]0:09[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFF00"]Time[/TD]
[TD="bgcolor: #FFFF00"]MBR[/TD]
[TD="bgcolor: #FFFF00"]MLN[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]0:01[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]0:02[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]0:03[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]0:04[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]0:05[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]0:06[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]0:07[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]0:08[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]0:09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=SUMPRODUCT(($A$2:$A$4=B$6)*($A7>=$C$2:$C$4)*(A7<=$D$2:$D$4),$B$2:$B$4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=SUMPRODUCT(($A$2:$A$4=C$6)*($A7>=$C$2:$C$4)*(B7<=$D$2:$D$4),$B$2:$B$4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[CodenameAter]

I'll give it a try! Thanks for the suggestion

 

[CodenameAter]

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!