Google-sheets – Look up rate for specific person in effect on or after a date

google sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-queryvlookup

In this Google Sheet, I have one sheet, "Transactions", with a rate entry that should be auto populated from a table in the "Rates" sheet. The formula should look up the rate for the listed person in effect on or after the date of the transaction.

Transactions sheet:

date      item          person   hours   rate    total
----      ----          -----    -----   -----   -----
1/1/21    design work   John     2       75      150
2/15/21   design work   John     2       90      180
3/5/21    design work   John     2       100     200

Rates sheet:

person   rate   effective date
----     ----   --------------
John     75     1/1/21
John     90     2/1/21
John     100    3/1/21
Nia      125    1/1/21
Nia      150    2/28/21
John     120    6/1/21

Best Answer

I have duplicated your "Transaction" sheet and renamed it "Erik Help." In that sheet, I've deleted your manual data in Column E and placed the following formula in cell E1:

=ArrayFormula({"Rate";IF(A2:A="",,IFERROR(VLOOKUP(C2:C&A2:A,QUERY({Rates!A2:A&Rates!C2:C,Rates!B2:B},"Select * Where Col2 Is Not Null Order By Col1 Asc"),2,TRUE),"NOT FOUND"))})

I encourage you to look at what just this portion does alone:

=ArrayFormula(QUERY({Rates!A2:A&Rates!C2:C,Rates!B2:B},"Select * Where Col2 Is Not Null Order By Col1 Asc"))

It creates a "mini-report" internally with the first column containing a composite of the name and raw-number date format as a single string, and the second column containing the rate at that time. (The raw-number format of a date is the number of elapsed days since December 31, 1899.) It's important to note that the QUERY is ORDER(ed) BY those name-date composite strings in ASCending order.

Using that, we can vertically look up (VLOOKUP) the same composite name-date entries from the first sheet within that QUERY, and return the value from the second column. The TRUE part of the VLOOKUP says that our lookup reference chart is in ascending order (which is why that was important to include in the QUERY, and that if no exact match can be found, we should find the nearest match before where a true match would have been.

The opening IF(A2:A="",, just means that we don't want anything in this column if there is nothing in Column A for that row.

The {"Rate"; ... } allows us to form another virtual array with the header and the formula results stacked one on top of the other (as signified by the semicolon).

ArrayFormula( ... ) indicates that this will process an entire range and not just one cell.

Related Topic