Lookup request dates for vacation tracker

Cindyann

New Member
Joined
Apr 24, 2014
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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)),"")
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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.
 
Upvote 0
As you have xl365 another option would be
Excel Formula:
=INDEX(FILTER(Requests2!$K2:$K10000,(Requests2!$F2:$F10000=$B$8)*(Requests2!$C2:$C10000>=L5)*(Requests2!$B2:$B10000<=L5),""),1)
 
Upvote 0
Solution

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

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
Back
Top