Google Sheets - How to return a value based on a date and a condition
Google Sheet - I have a table with sales_targets per sales_staff that are effective until a new date. In the table below, the monthly target for Bob from 2 Mar 2019 was $11,000, which then changed on 6 Apr 2020 to $12,100.
Google Sheet: https://docs.google.com/spreadsheets/d/1qqRBiEd9TmjBuTAlrukNIodsAjUnEf-cnMMo3VCpoVc/edit?usp=sharing
I need to return a value (Sales_Target) based on two input values (the Date and Sales_Name). It must return the Sales_Target for the Staff_Name for the period when that target was active. Once the date is changed, it will need to select the new target for the Sales_Staff based on that selection date/period. Hopefully this table below can explain it better:
I can do vlookups, but that is where my talent ends. The date condition for a period of time until the next date has got me stumped. I've tried to look online, with INDEX / MATCH, MAXIFS functions, but I haven't come right.
You may try:
=map(E2:E,F2:F,lambda(e,f,if(or(e="",f=""),,let(Σ,sort(ifna(filter(A:C,B:B=f)),1,1),xlookup(e,index(Σ,,1),index(Σ,,3),+index(Σ,,3),-1)))))
