# Vlookup & IF statement, based on dates

#### Kelli Webb

##### Member
Hello,

I would like some assistance to create a formula, based on the following.

For each employee (in column A) return the Dept (Column L) based on when the date falls or or between the dates provided in columns J & K, for each Employee using the dates in column D.

What the Dept should be is in column E.

Any assistance will be greatly appreciated, thank you

Kelli

#### Attachments

• 22.1 KB Views: 7

#### pecoflyer

##### Active Member
Correcting the date in K4 and removing a duplicate Team Leader row, the following works ( see yellow range)

#### Attachments

• 22.6 KB Views: 8

#### bosco_yip

##### Excel Ninja

In E3, formula copied down :

=INDEX(L\$3:L\$14,MATCH(1,INDEX((J\$3:J\$14<=D3)*(0+TEXT(K\$3:K\$14,"0;;99999")>=D3)*(G\$3:G\$14=A3),0),0))

Regards

#### Kelli Webb

##### Member
Correcting the date in K4 and removing a duplicate Team Leader row, the following works ( see yellow range)
Thank you Pecoflyer! I still have so much to learn, Kelli

#### Kelli Webb

##### Member

In E3, formula copied down :

=INDEX(L\$3:L\$14,MATCH(1,INDEX((J\$3:J\$14<=D3)*(0+TEXT(K\$3:K\$14,"0;;99999")>=D3)*(G\$3:G\$14=A3),0),0))

Regards
Thank you Bosco_yip, you never disappoint.

Take care, Kelli

#### Peter Bartholomew

##### Well-Known Member
Code:
``````= LET(
withinDate?, (Start<=[@Date])*((End>=[@Date])+(End="")),
filteredEmpl, IF(withinDate?, Employee),
XLOOKUP([@Employee], filteredEmpl, Dept) )``````

#### Attachments

• 23.1 KB Views: 1

#### Kelli Webb

##### Member
Code:
``````= LET(
withinDate?, (Start<=[@Date])*((End>=[@Date])+(End="")),
filteredEmpl, IF(withinDate?, Employee),
XLOOKUP([@Employee], filteredEmpl, Dept) )``````
Hi Peter, thank you for your suggestion, is this VB code? I don't think I'm quite there just yet.

Although I did write a code to split out into different tabs, based on a column i.e. Managers name.

So when I have time to dabble, I do try to push the envelope lol.

thanks again & take care,

Kelli

#### Peter Bartholomew

##### Well-Known Member
@Kelli Webb
No, it is a standard worksheet formula. Unfortunately it is only 'standard' in Excel 365 and, even at that, it has yet to be rolled out to the semi-annual update channel. Once one recovers from the initial shock, the alternating pattern of variables and fragments of formula makes the overall formula reasonably intelligible.