# Joining Two Working Formula's

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

• 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 '19 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 '19 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 '19 at 15:24