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
1 Answers
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))))))
Related