Vlookup with IF

[gossv]

Hi I am trying to create a VLookup that also reflects the relevant dates:

01/10/05 Cronulla Adam
01/05/06 Cronulla Steve

From Oct 05 to April 06 I would like Cronulla to reflect Adam's name

From March 06 to July 06 I would like Steve's name to be against Cronulla


Date Territory
01/03/06 Cronulla
01/04/06 Cronulla
01/05/06 Cronulla
01/06/06 Cronulla
01/07/06 Cronulla

Can anyone help me with a formaula please?

Thank you - Ginny

 

[Dufus]

Ginny,
Don't these two date ranges overlap?

From Oct 05 to April 06 I would like Cronulla to reflect Adam's name

From March 06 to July 06 I would like Steve's name to be against Cronulla

Dufus

 

[JohnnyTightlips]

Try this formula, it should work. It assumes that 'Cronulla' is a variable, and that there is a table starting cell A1

=INDEX($C$1:$C$2,MATCH(1,($A$1:$A$2<=A7)*($B$1:$B$2=B7),1))

Where C1:C2 is Steve and Adam's name
A1:A2 are the dates 1/10/05 and 1/5/06 respectively
B1:B2 is the location (Cronulla)

It is an array formula and needs to be entered with Ctrl+Shift+Enter

A7 is the date being looked up, B7 is the location. Sorry, cant use the HTML maker at work

EDIT: Assumed you meant May 06 in your post

 

[gossv]

Hi Dufus, I feel bad calling you that!

The dates will never overlap but it may be that Adam stops controlling the territory on the 3rd May and Steve takes over on the 10th. In this case I would like it to list Steve as the owner for May, however if he doesn't take it over until the 20th I would like Adam listed as the owner.

Is it possible to use the 15th of the month as a marker or is that making it too complex?


Sorry it is meant to read:

From Oct 05 to April 06 I would like Cronulla to reflect Adam's name

From MAY 06 to July 06 I would like Steve's name to be against Cronulla