Search code examples
dateif-statementgoogle-sheetsvlookupnested-if

Google spreadsheet formula with several conditions

I have the following data - number, value, date. I need to find the right percentage based on the following conditions: 0-10 - 40% 11-20 - 50% 21-40 - 60% and a few branches for 70%: If date>='2022-01-01' then, for 70%, number should be >41 and value>10, if date<'2022-01-01' then, for 70%, number >41 or value>10 Here is my test table - https://docs.google.com/spreadsheets/d/1HpNTirimG76bxNy0Efbzqpj8yCtFCGQ3vDIGaSAH66o/edit?usp=sharing

Solution

  • try:

    =INDEX(IF(A2:A="",,
 IF((C2:C>=DATE(2022, 1, 1))* (A2:A>41)*(B2:B>10),  70%, 
 IF((C2:C< DATE(2022, 1, 1))*((A2:A>41)+(B2:B>10)), 70%, 
 IFNA(VLOOKUP(A2:A, {0, 40%; 11, 50%; 21, 60%}, 2, 1))))))