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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
As long as column A is sorted ascending you could use something like....

=LOOKUP(A8,$A$1:$C$4)
 
Upvote 0
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
 
Upvote 0
Thank You Weazel that one worked the best! I knew it could be simple just couldn't remember what the formula was.
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top