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
  • 1
    Currently, we don't know the names of either sheet, the ranges containing the data, whether your dates are real dates or text, etc. The most efficient and effective way to receive help is to share a link to a sample spreadsheet (e.g., one with exactly the information above in it, placed where it will actually be in your real sheet and representing as closely as possible your real data—like if you will use first and last name then use first and last in your mock data, not just first). If you choose to do this, be sure to set the link's Share permission to "Anyone with the link..." and "Editor."
    – Erik Tyler
    Feb 18 '21 at 1:06
  • I edited the question and added a link to a sheet. Feb 19 '21 at 16:46
  • 1
    See my posted answer below. If this meets your needs, please take a moment to mark that post as "Best Answer." This will indicate to other volunteers here that the issue is resolved, as well as help future site visitors more quickly find answers that match their own questions of similar nature. Thanks.
    – Erik Tyler
    Feb 20 '21 at 0:00

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.