Google spreadsheet formula with several conditions

January 31, 2022

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