Excel 2013
Attachment: "Example Lookup.xlsx". Tab 1 "Programs" contain date ranges for when an employee was in a particular "program", tab 2 "YTD" contain employee check dates for the year. The lookup function I'm using,
{=LOOKUP(2,1/IF(YTD!A2=Programs!$A:$A,YTD!B2>=Programs!B:B),Programs!C:C)}
correctly pulls the program for the employee based upon the "YTD" Check_DT being greater than or equal to the "Programs" Event_DT. The results in the "YTD" worksheet show the desired results accurately in column C.
However, the "Programs" will have around 100,000 records and the "YTD" will exceed 500,000 records. Is there a more efficient technique that could be used in this example? Thanks in advance for any guidance!
Bookmarks