retrieve value from list based on 2 criteria incl date

Hi,

I have 2 lists. The first one is a large list (+30.000 lines) of a timesheet. This list contains the resource names, period, and actual effort.
I want to add to each line the rol this resource has in that period. In the second list (+100 employees) I have all resources with the skill level and the first period ([yymm], e.g. 1411 is November 2014) in which the resource started in that role.

What I am looking for is a formula that can retrieve the skill level from the second list, but also taking into account the date from which that resource has started in that role.
For example:
employee Anne has started working in period 1411 in role AAA and changed roles again in 1510 and 1601
In the time sheet I have records for periods 1506, 1511, 1601 and 1602
So when there is a record for period 1511 I would expect to show role BBB as Anne changed roles from AAA to BBB in 1510.
See example file attached...

I Have tried to use the vlookup, however, this does not do what I need.
=VLOOKUP([@[Period+Name]];Table2[#All];3;TRUE)

Is there way worksheet functions can do this?

Hi
Try this
=INDEX(Table2[Level],AGGREGATE(14,6,(Table2[Resource]=Table1[@Resource])*(Table2[Date]<=Table1[@Periode])*ROW(Table2[Column1]),1)-1)

Hi José, That did work on the example file I attached. Nice formula.

However, I did not do the trick in my original file. :-(
Maybe I over simplified the example file....
I created a second example file by deleting worksheets and data from my original file. Hopefully this will give a better insight in what I am looking for.

Thanks,

Hi
Try to use
=ÍNDICE(Table_resources[Role];AGREGAR(14;6;(Table_resources[Resource]=[@Resource])*(Table_resources[Per Datum]<=[@Period])*LIN(Table_resources[Date+Rec]);1)-1)

Formula:

Please Login or Register  to view this content.

Please note: the formula is not an array formula, ie don´t use CSE.
See the file Example2.xlsx

Hi José,
For resource "Lenny" I see 3 lines in the Resources table. When I place a filter on the TimeSheet table for this resource I see that all lines have the same role. I would expect to see 3 different ones...

I guess the formula should look for the first record in the Resources table that has a date not larger than the period in TimeSheet and not the first smaller than or equal to.
Would it help to change the sort order of the Resources table to Z>A and then look for the first record which is smaller than the Period in TimeSheet?

I like the INDEX formula, because it looks more flexible than VLOOKUP, however I do not fully understand the formula. Is the "ROW(Table_resources[Date+Rec]" part really required? What does this do?

Cheers,

I noticed that the order of sequence for the Resources list is important. I also changed the combined field for Name and Period from Period&Name to Name&Period and then sorted the list on that Name&Period field. This way all lines for each resource are grouped together and the index formula worked better.
I am still working on the details to have the formula match my needs for 100%

to be continued...

What does this +0 do?
It did the trick, but I do not understand why...

Hi kammend
Your column Table_TimeSheet[Period] has type text.
to convert text to number you can use +0
="2345"+0 gives you 2345 as number

Regards

cool

Regards, Daniel