0

I have the following working formula

Located in K2 =IF(INDEX(Details!B18:B81,MATCH(C2,C2,0))<>C2,S30,"0")

So I want it to only use the number in S30 if the date in C2 is not in a range of dates on the Details Page within B18:B81

Now I need to use this formula on the below cells, however, there is an additional check that needs to be done

This working formula is

Located in K3 =IF(AND(C3 < TODAY()+1, J2<>""), K2+S$30, )

So I am wanting the above formula to do what it does if the date in C3 is not in the range on the Details Page within B18:B81 (The date range is either Sunday's or days closed)

If C3 is in the range I want it to add Zero instead of the number from S30

The attached image gives you the cells referenced and for this formula, S30 is equal to 2

enter image description here

|improve this question
  • So this is weird, this formula works, however, only for the first date in the index/match range: =ARRAYFORMULA(IF(AND(INDEX(Details!$A$18:$B$81,MATCH(C2,C2,0),1)<>C2,C3<TODAY()+1,J2<>""),K2+S$29,K2+0)). If I modify the formula to be like this =ARRAYFORMULA(IF(AND(INDEX(Details!$A$18:$B$81,MATCH(C15,Details!$B$18:$B$81,0))<>C15, C16<TODAY()+1, J15<>""), K15+S$29, K15+0)) it works for the second date, however, if I drag it down all I get is #N/A with the error being Error Did not find value '43570' in MATCH evaluation. Thoughts / Suggestions? – woodie OS May 18 at 14:23
  • Tried this formula; =(IF(AND(LOOKUP(C2,Details!$B$18:$B$81)<>C2,C3<TODAY()+1,J2<>""),K2+S$29,K2+0)) and get the same error, Error Did not find value '43556' in LOOKUP evaluation. – woodie OS May 18 at 14:29
  • OK with this formula; =ARRAYFORMULA(IF(AND(INDEX(Details!$A$18:$B$81,MATCH(C15,Details!$B$18:$B$81,0))<>C15, C16<TODAY()+1, J15<>""), K15+S$29, K15+0)) I have figured out that I get the #N/A when the reference date (aka C2) in the formula is not listed in the Date Range searched – woodie OS May 18 at 15:24
0

SOLVED

Ended up using the following formula (after creating an Open/Closed Status column)

=IF(AND(C2="Open",D3""),L2+T$29, IF(AND(C2="Closed",D3""),L2+0,""))

|improve this answer

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.