Find interval in data set given a date (i.e. exact match not possible)

[tanjan]

I have a table with info on what state a given item is in at a specific point in time.

-----------------------------------------------------
Item | State | Start Date of State
------------------------------------------------------
Item1 | Not started | 2013-jan-1
Item1 | Being prepared | 2013-feb-5
Item1 | Online | 2013-mar-10
Item2 | Not started | 2013-jan-15
Item2 | Being prepared | 2013-mar-8
--------------------------------------------------------
“Item1” was in the “Being prepared” state in the interval 2013-jan-1 to 2013-feb-4 etc.

I would like a function that returns the State given Item name and a date as parameter.

I usually handle stuff like this by use of vlookup() og match(index()), but I can’t get a hang of it since the data given as parameter will normally not have an exact match in the data set.

Is it possible to come up with a function without having to use VBA?

... and if not, can someone help never-used-VBA-before with some simple VBA code for it?

Any help is much appreciated
tanjan
(My first post so I hope I have done it correctly)

 

[AlphaFrog]

Try this...

<br />

Book1
	A	B	C	D	E	F	G
1	Item	State	Start Date of State		Lookup
2	Item1	Not started	2013-Jan-1		Item	Date	State
3	Item1	Being prepared	2013-Feb-5		Item1	2013-Feb-28	Being prepared
4	Item1	Online	2013-Mar-10
5	Item2	Not started	2013-Jan-15
6	Item2	Being prepared	2013-Mar-8
Sheet1

Cell Formulas
Range	Formula
G3	=LOOKUP(2,1/((A2:A6=E3)*(C2:C6<=F3)),B2:B6)

 

[tanjan]

Thanks AlphaFrog. Brilliant

 

[tanjan]

Hi again

Unfortunately it seems the formula does not perform in the context I use it.

I want to make an overview of states for the different items over time:


xxxxx | Week1 | Week2 | .... | Week52
----------------------------------------------
Item1 |
Item2 |
Item3 |

... and then showing the states by use of color inside the table

However, already after 20 cells the update of the table starts to be slow

Is there any better option regarding recalculation of the worksheet?

Thanks
Tanjan

 

[tanjan]

Hi again

It turned out the worksheet was somehow corrupted. After carefully moving the functionality to a new worksheet it works fine. Cumbersome ... but really nice.

Thanks again AlphaFrog