'Sheets header row ARRAYFORMULA to look up rate based on the job's turnaround AND date received within a range of dates
I've got a Google Sheets workbook with two sheets: Jobs
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Turnaround | Received | Rate | Pages | Total |
| 2 | Standard | 12/2/2021 | $0.40 | 204 | $81.60 |
| 3 | Rush | 12/9/2021 | $0.60 | 79 | $47.40 |
| 4 | Rush | 12/29/2021 | $0.60 | 24 | $14.40 |
| 5 | Standard | 1/1/2022 | $0.45 | 81 | $36.45 |
| 6 | Standard | 1/2/2022 | $0.45 | 137 | $61.65 |
| 7 | Standard | 1/5/2022 | $0.45 | 95 | $42.75 |
| 8 | Standard | 1/15/2022 | $0.45 | 162 | $72.90 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Turnaround | Base Rate | Start Date | End Date |
| 2 | Standard | $0.40 | 9/1/2021 | 12/31/2021 |
| 3 | Rush | $0.60 | 8/17/2018 | 6/10/2022 |
| 4 | Expedited | $0.80 | 8/17/2018 | 6/10/2022 |
| 5 | Daily | $1.00 | 8/17/2018 | 6/10/2022 |
| 6 | Standard | $0.45 | 1/1/2022 | 6/10/2022 |
I'm trying to use an ARRAYFORMULA in Jobs!C1 to look up the value in Rates!B:B where the Turnaround in Jobs!A:A matches the Turnaround in Rates!A:A and the Date Received in Jobs!B:B falls on or between the Start Date in Rates!C:C and End Date in Rates!D:D.
The idea is that rates may change over time, but the job totals will still calculate using the correct rate at the time each job came in.
I know I can't use SUMIFS with ARRAYFORMULA, so I tried using QUERY, but this only populates the rate for the first job.
={"Rate";
ARRAYFORMULA(QUERY(Rates!A:D,
"select B where A contains '"&Jobs!A2:A
&"' and C < date'"&TEXT(Jobs!B2:B, "YYYY-MM-DD")
&"' and D > date'"&TEXT(Jobs!B2:B, "YYYY-MM-DD")&"'",0))}
I'm okay with adding helper columns if needed. I'm trying to avoid having to manually fill the formula down the column as jobs are added.
Here is a link to the workbook: Job Rate Lookup By Turnaround + Date Range
I appreciate any help on this.
Solution 1:[1]
try:
={"Rate"; ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B, SORT({
FILTER(Rates!A2:A, Rates!A2:A<>"")&Rates!C2:C, Rates!B2:B}, Rates!C2:C, 1, Rates!A2:A, 1), 2, 1)))}
Solution 2:[2]
When using ARRAYFORMULA you won't be able to use QUERY in order to get the whole array of values as it will only return the first value that is found.
I created a formula that matches the value using VLOOKUP however I had to modify the name in Jobs from Standard to Standard 2.
This is the formula:
=IFERROR(ARRAYFORMULA(VLOOKUP(A2:A,Rates!A2:D6,2,0)))
These are the results:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Gabriel Carballo |