Lookup request dates for vacation tracker

[Cindyann]

Good Afternoon,

Thanks in advance for any assistance provided.

I have a spreadsheet that I am using to track the vacation request of my employees for the year. One tab has the requested start and end dates and the second tab has the employees names and the dates filled out across the year. This is a multiple criteria formula where it looks for the employee, looks at the amount of days requests and looks at the type of vacation requested. I am using the following formula to return the data I need. Now to be clear the formula does work but makes the file take up to 15/20 minutes to load. Is there an easier way to get this data or a lighter formula (hopefully that makes sense) that can achieve the same result?

=IFERROR(INDEX(Requests2!$K:$K,MATCH(1,INDEX((Requests2!$F:$F=$B$8)*(Requests2!$C:$C>=L5)*(Requests2!$B:$B<=L5),0,1),0)),"")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

One way to improve the performance regardless of version is not to use whole references, but to limit them to a sensible range.

 

[Cindyann]

Thank you noted. I have updated my excel.

Let me attempt to shorten the formula to 10,000 rows to see if that makes a difference.

 

[Fluff]

As you have xl365 another option would be

=INDEX(FILTER(Requests2!$K2:$K10000,(Requests2!$F2:$F10000=$B$8)*(Requests2!$C2:$C10000>=L5)*(Requests2!$B2:$B10000<=L5),""),1)