Conditional Formatting

[Brian]

I setup the following formula in conditional formatting to turn the cell
yellow if the amount listed in cell B25 is not found in column T.
I then also added the beginning part of the formula to not turn yellow if
the date listed in cell A1 is a day in the future:

=0=IF(A1>=TODAY(),1,IF(ISNA(VLOOKUP(B25,T2:T1500,1,0)),0,1))

I want to add one more condition to the forumla that says if the amount
listed in cell B25 is 0, don't turn the cell yellow.
How can I tweak the formula listed above?
Right now the cell is turning yellow because a 0 is in cell B25, but not
found in column T.

Any help would be appreciated!

 

[Sean Timmons]

Then, just make a condition of =B25=0 as yoru condition 1 with no format
applied and make the below your 2nd condiiton.

=OR(A1>=TODAY(),ISNA(VLOOKUP(B25,T2:T1500,1,0)))

 

[Jacob Skaria]

Paste the below as CF formula

=AND(A1<TODAY(),B25<>0,COUNTIF(T:T,B25)=0)

If this post helps click Yes

 

[Jim Thomlinson]

Conditional formats want formulas that resolve to true or false. True means
apply the format. False means no format. To that end you can use AND very
effectively.

=and(A1<TODAY(), B25<>0, ISNA(VLOOKUP(B25,T2:T1500,1,0)))

I think my boolean is correct.

 

[Jim Thomlinson]

Note that CountIf will treat strings and numbers the same. To that end
countif can find the value while vlookup will evaluate to NA. For example if
123 is a string and you try to find it in a group of numbers it will return
NA. Countif however would find it. That is a feature that I leverage in my
reference functions such as VLookup or match but I am not sure that it is
appropriate here. That is for the OP to decide...

 

[Jacob Skaria]

Jim, thanks for explaining. Appreciate that..

When I ready the post initially it seemed to me that column T is 'column of
amounts'.

If this post helps click Yes