Finding the value within a date period for each employee

[DanzaNZ]

Hi guys and girls

I have a data problem that I hope can be fixed with formulas.

Quick background: I received two reports which have employee numbers, positions and agreements. The problem is the agreements have different dates to the positions, which makes it hard for me to vlookup the award on the position.

Scenario: There are two sheets, "Positions" (has info such as emp number, position and position start date), and "LevelChange" (has info such as emp number, award and agreement start date).

Problem: The employee agreements need to be paired in with the position dates that they fall in.


I have thought about a normal vlookup, however this would not ensure the correct agreement is being paired with the right position period. Next i thought about a between statement, except that's a bit above my skill level.

I have attached a book that demonstrates the two sheets of data and an additional sheet would would show examples of desired results

Hope someone has a solution!

Thanks
D

[Sam Capricci]

I have one that is close to it but I can't reconcile a couple, for one 20004 doesn't start until 6/3/2012 in positions but your earliest in level change is 6/2/2009 so writing a between statement for what is in column C and column D returns an #N/A because 6/2/2009 isn't between 6/3/2012 and 12/31/9999. There are others like that, for example emp number 20006

[DanzaNZ]

I'd love to hear your solution!
The NA you refer to might be genuine which means the agreement data doesn't exist for the position date range.

I'm keen to try whatever you have.

[Sam Capricci]

this appears to work,
=IFERROR(INDEX(LevelChange!$B$3:$B$17,AGGREGATE(15,6,ROWS($1:1)/(LevelChange!$A$3:$A$18=A2)/(LevelChange!$C$3:$C$18>=C2)/(LevelChange!$C$3:$C$18<=D2),COLUMNS(H:H))),"")
don't ask me how it works, I know some parts but just played with it until I got the whole thing working.

the columns part can be changed to any column but only the same, for example COLUMNS(A:A) will work too.

[DanzaNZ]

Awesome! I'll try it on the real data when I'm at work in an hour.
Will advise of outcome.

[DanzaNZ]

Hi again, I'm not sure what Column H in the formula references to, as my data test sheets don't use that column

[Sam Capricci]

It’s necessary for the formula to work but doesn’t need to reference any active cells, as I noted in post #4 you can reference any column, A:A or B:B, it is returning 1.

[DanzaNZ]

Unfortunately I'm not sure it's work... I'll upload some of the actual data

[DanzaNZ]

Heres a sample of the data.
For the yellow cols E,F and G there needs to be showing the relevant values from the LevelChange sheet within the position date ranges. Hope this makes sense

[Sam Capricci]

A reason it will not work is because your Date Start and Date End in LevelChange are NOT real dates. if you can convert them to dates so excel recognizes them as dates then the formula should work with just some adjustments to columns and ranges. You can test if they are dates by putting =ISNUMBER(E2) and same for F2 in level change, both will return FALSE, now if you put the same in the position tab and point to either C2 or D2 it will return TRUE showing the "dates" in that tab are dates (which are merely numbers formatted to look like dates).

[DanzaNZ]

Hi again,

Im still sure it doesn't work properly.... I changed the return value from column B to A in the formula and for a few rows it shows the incorrect employee number... still stuck on this 😢

[AliGW]

Please attach the latest version of the sheet showing the errors AND the expected results (manually mocked up).

[DanzaNZ]

Hi!

Attached is the latest data, which has two sheets, one being Position and the other LevelChange.

The idea is to use vba or an excel formula to fill in the yellow on the Positions sheet to match the employee number and be in the date range of the LevelChange sheet.

I have filled in the first 9 rows to illustrate the desired results.

[JeteMc]

The formula that I have is similar to Sam's:

Please Login or Register  to view this content.

The formula returns the same results as in the 9 rows that were manually filled.
I am assuming that the start date on the LevelChange sheet needs to occur on or before the start date on the Position sheet, since the start dates are all that are mentioned in post #1.
If that is an incorrect assumption then please explain how you made the decisions when you were filling the values manually, as those would be the same "decisions" the formula should make.
Let us know if you have any questions.