Lookup formatting issue when new row added to table

[stuckonweb]

Have made great progress creating a file to track contracts, and other than the file changing the hyperlinks twice (grrrrr....) the only (current) issue I have is with a lookup command and Excel tables.

I have a field to show the status of the contract - Active, Paid, or Expired, which changes the color of the contract header based on the status.  If the contract tracks the payments made then it uses a table.  The status references the last cell in the Balance column of the table to find if the balance is zero and therefore mark it as paid. It works great except for an issue of when a new row is added to the table the status cell adopts the format of the table (Calibri 11, black).

The formula is:  =IF(LOOKUP(3,1/($G9:$G10<>""),$G9:$G10)=0,"PAID",IF(OR($G4="",$G4>=TODAY()),"ACTIVE","EXPIRED"))

Why is it doing that and how do I get it to not do that?  Any help would be greatly appreciated!!

Using Excel 2019 on a Win10 pc. (I would attach a file, but don't see any way to do so?)

[#2]

@stuckonweb I remembered helping you with this schedule a while ago and found that the issue at the time was related to conditional formatting. Did you change anything in that area?

[#3]

As far as the conditional formatting, no, there have been no changes and it still works fine. I am changing the fill color of 3 rows based on the word in the status cell by using a reference to a hidden column. (Again, thank you so much for your suggestion!)

In order to determine what word appears in the status cell I needed to use tables to track a contract's balance. The lookup formula looks at the last cell in the Balance column and if 0.00 it puts the word PAID. If not zero it looks at the contract's expiration date and if it is older than today it is EXPIRED, otherwise it is ACTIVE. The words themselves work fine, but when I add a new row to the table the formatting of the word changes to match the formatting of the table.

[#4 · Accepted solution]

I was able to solve the problem by making the formula =IF(LOOKUP(3,1/($G9:$G10<>""),$G9:$G10)=0,"PAID",IF(OR($G4="",$G4>=TODAY()),"ACTIVE","EXPIRED")) into an array. Now the font formatting doesn't change when adding a new row.