Finding a title in a range of multiple dates

[sk1mcguire]

Hello Sirs!

I have a file that lists a title as of a certain date. In the columns I have Employee Number, Title, effective date of title. Take one for example:

Emp No Title Date
5298 Staff Acct 7FEB2007
5298 Staff Acct1 8MAR2009
5298 Asst Controller 12APR2010
5298 Controller 14JUL2011

In another file I have earnings for fiscal year 2016 which is 1APR15 to 31MAR16.

I have to do this for three fiscal years of earnings FY16, FY17 and FY18. All in separate files.

I need to return the title that was in effect as of 31MAR16. For employee 5298 it would be Controller

I hope this isn't too confusing and I have presented a clear picture!

 

[Special-K99]

Your example data implies that all Emp No. will be together in the file, ie 5298 will not occur later in the file.
Is this the case?

Are these dates text values or proper dates?

 

[sk1mcguire]

Thanks Special-k99!

That is correct sir. 5298 will be sorted all together and the dates will be proper dates ie 04/30/2015 (US Format)

Thanks so much!!

Jonathan

 

[Special-K99]

Does this work?

=VLOOKUP(E1,C1:C4,1,1)

where E1 is Title and C1:C4 is the start date

 

[sk1mcguire]

That does it!!!!

Thanks a million Special-K99.

God Speed - Jonathan