Vlookup with IF

gossv

Board Regular
Joined
Jul 8, 2005
Messages
141
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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

Board Regular
Joined
Aug 13, 2006
Messages
94
Try this formula, it should work. It assumes that 'Cronulla' is a variable, and that there is a table starting cell A1

Code:
=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

Board Regular
Joined
Jul 8, 2005
Messages
141
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
 

Forum statistics

Threads
1,136,266
Messages
5,674,728
Members
419,523
Latest member
Urnovio

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
Top