I have a spreadsheet with all employees work reports with info about time spent on a job and other details. I can't figure out how to get the fee the employee is supposed to get paid because I can either set the employee's fee manually for every single entry (not desirable) or there should be a formula that could automatically grab that data for me

Basically what I have in mind is some (maybe?) Arrayformula that for every date in the employee report entry searches for the matching hourly fee that fits the date the fee has been updated but when there is another fee update, it won't affect the older fees

Employee fees sheet

Reports overview per employee

I have tried VLOOKUP with ARRAYFORMULA but I could only get the latest fee and that is not good because that would also change the payments for older entries

EDIT: forgot to provide a link to a dummy sheet with the scenario mentioned DUMMY sheet

  • hI - can you comment in english what is the logic that you want out of your spreadsheet. iT shoud lookup the fee table, and if there is an exact date match, it should copy the value from the table. If there is not, what should happen?
    – Oren
    Aug 14 at 3:20
  • Hello, if an employee has a set fee starting today I want the ongoing fee to be the current one from this day but for older periods there should still be the previous fee set until the recent change. Example: fee starting Jan 2021 was 20 fee sarting Aug 2022 is 25 So I just add the new date and price in the Fees sheet and the overview per employee will be automatically counting the correct fees regarding date. Automated formula like ARRAYFORMULA is highly prioritized because there will be even more than one entry per employee per day Aug 14 at 8:41
  • I added hopefully enough info in the Employee1 sheet with my formula that I thought might be aiming the correct direction but my knowledge ends there Aug 14 at 9:13

1 Answer 1


you should use something among those lines:


make sure your fees table dates are in ascending order. I just removed the (;FALSE) as the last argument and it worked (don't ask me why)

  • I just added the ARRAYFORMULA =ARRAYFORMULA(IFERROR(VLOOKUP(D2:D;Fees!$C$2:$D$4;2))) and it works exactly as needed! I love how sometimes it seems like a more difficult formula to make but the solution is just nice and simple. I am forever grateful for your help Aug 21 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.