Add hourly rate to line items based on line item date

Hello, I'm trying to set up a system using google forms and google sheets to keep track and analyse my business's employee's hrs and wage.

My idea is:

Use a simple Google Form to for employees to submit their daily hrs. (The Name will be a dynamic list using the Form Ranger Addon which will be populated by a google sheet which has a list of all employees names.)

The Google Form will be linked to this Google Sheet which holds the columns Employee | Date | Start time | End time and then the calculated columns Total Hrs and Hourly Rate.

This is where I get stuck! ​ I want the Hourly Rate to be an arrayformula and to be dynamic based on the values of a different sheet. Let me explain!

Employee wages might change throughout the year and I would like the hourly rate line amount accounting to each employees daily submission to be dynamic based on the employees changing rates.

I thought to have a separate sheet where I will fill in employees hourly rate and every time the rate changes. This will look like this.

So finally:

How do i write an array formula that returns the employees hourly rate based on the date in the row (output of daily from submission)?

I was thinking some kind of nested XLOOKUP but I'm totally stuck.

Formula

This formula will return the hourly rate based on the date worked ignoring future increases that do not apply.

``````=LAMBDA(r, INDEX(r, XMATCH(dateWorked, INDEX(r,, 2), -1), 3))
(SORT(FILTER(rateTable,INDEX(rateTable,, 1)=employee),2, FALSE ))
``````

Conditions

• "dateWorked" is the day the hours are worked (in form);
• "rateTable" is a 3 column range with
1. employee names in column 1;
2. dates in column 2 (effective date for the new hourly rate);
3. hourly rates in column 3
• "employee" is the employee name (in form)

Notes

1. LAMBDA is used to avoid repeating the initial SORT and FILTER and use `r` in its place
2. "rateTable" is filtered for the employee and then sorted in descending order by date: `r`
3. XMATCH is used to find the date in `r` that either matches the date worked or is older.
4. INDEX returns the hourly rate in the same row as the XMATCH-ed date

BYROW Version (single formula)

This version takes the formula above and uses BYROW to perform the same LAMBDA formula for each row in the name column (column A).

• a named range `employee` was created `A2:A500`,
• BYROW passes each row `employee` into a LAMBDA function where the current row is represented by the arbitrarily chosen name `e`
• If `e` is empty (no data entered) the formula is skipped: `IF(e<>"", <formula>, IFERROR(1/0)` (`IFERROR(1/0) is a sheets trick to return a genuinely blank result) ;
• to get the "employee" from the current row (e.g. `A2`) I can now use the `e` in the formula;
• for the "dateWorked" in the formula I can use the same `e` variable but use OFFSET to indicate where the data is located in relation to "employee". In this "dateWorked" is `0 rows` and `1 column` away from the "employee" column: `OFFSET(e, 0 , 1)` `
``````=BYROW(employee, LAMBDA(e,
IF(e<>"",
(LAMBDA(r, INDEX(r, XMATCH(OFFSET(e,0,1), INDEX(r,, 2), -1), 3))
(SORT(FILTER(rateTable,INDEX(rateTable,, 1)=e),2, FALSE ))),
IFERROR(1/0))
))
``````