Help with VLOOKUP to return corresponding value from nearest date value

[sfoc]

Our point in time reporting capabilities are lacking so hoping to find some help here.

I have a list of employees who were active as of 3/31/21. On another tab is a list of pay history with effective dates for each rate of pay. I need to somehow pull in the salary value from the pay history tab where the effective date is closest to and/or less than 3/31/21.

Based on the attached example, the correct value should be 74000 as this is the latest effective date which is nearest to but less than 3/31/21.

Thank you in advance!
sfoc

[protonLeah]

custom number format for C1: "Salary as of" m/d/yyyy
Then in C2:

Please Login or Register  to view this content.

[sfoc]

That solution would work in the example provided but with the live data containing thousands of unique employee ID's and dozens of effective dates per employee, there is a tendency for things to get a bit weird and wrong salaries are pulled in.

Since we're dealing with a lot of unique file #'s, how can we incorporate the file # into the lookup value?

[GeoffW283]

Starting from Ben's post #2 workbook, in C2 please try:

Please Login or Register  to view this content.

Relative to the prior vlookup() formula this formula adds a check that the filenames match.
Edit: This is an array formula and must be committed via CTRL-SHIFT-ENTER

Note that with both this and the prior vlookup() formula the sheet2 dates must be in chronological order.

If this still doesn't do it, then please upload a representative data set that includes specific examples of any "weirdness" that we need to accommodate.

[sfoc]

Thank you Geoff...that worked like a charm!

By any chance and based on your example, is there a way to also pull in the salary previous to the 74,000 in column D?

[TMS]

Maybe just this:

Please Login or Register  to view this content.