# 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))
))
`````` 