• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup & IF statement, based on dates

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

  • Chandoo - Vlookup based on IF dates fall between.xlsx
    22.1 KB · Views: 8
Correcting the date in K4 and removing a duplicate Team Leader row, the following works ( see yellow range)
 

Attachments

  • Chandoo - Vlookup based on IF dates fall between.xlsx
    22.6 KB · Views: 10
Follow pecoflyer's adjusted file, here's another formula solution solution.

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
 
Follow pecoflyer's adjusted file, here's another formula solution solution.

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
 
Code:
= LET(
  withinDate?, (Start<=[@Date])*((End>=[@Date])+(End="")),
  filteredEmpl, IF(withinDate?, Employee),
  XLOOKUP([@Employee], filteredEmpl, Dept) )
 

Attachments

  • Chandoo - Vlookup based on IF dates fall between.xlsx
    23.1 KB · Views: 1
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
 
@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.
 
Back
Top