Search code examples
arraysgoogle-sheetsgoogle-sheets-formulavlookuparray-formulas

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

Rates

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

  • 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)))}

    enter image description here