Conditional Formatting using multiple criteria and INDEX MATCH

[Gesyca_is_joy]

So I have an Excel workbook with two tabs; What i am trying to do is write a conditional formatting formula that will highlight the account number red if I enter an account number that is not funded.
The formula I am thinking of has 3 parts, which I have broken out below, but I can't figure out how to get the formula to work, am I over thinking this?

Full Formula Currently: =AND($A2<>"",$M2="")=INDEX(INDIRECT('Chart of Accounts'!$C$4:$C$1002),MATCH($H2,INDIRECT('Chart of Accounts'!$A$4:$A$1002),0))=<>

• =AND($A2<>"",$M2="")
  • Checks to make sure the entry has an invoice number and not a check number (this is because funding changes over time and I don't want to see the formatting on records that have already been paid.
• =INDEX(INDIRECT('Chart of Accounts'!$C$4:$C$1002),MATCH($H2,INDIRECT('Chart of Accounts'!$A$4:$A$1002),0))
  • Performs and INDEX MATCH on the Account number to match it with Account number on Tab 2 and check if that account has Funding
• =<>
  • If the INDEX MATCH formula above returns the cell in column C with any value then it is funded, if that column is blank then the account has no funding.

Tab 1: Invoices:

• Column A: Invoice Number
• Column M: Check Number
• Column H: Account Number

Tab 2 Chart of Accounts:

• Column A: Account Number
• Column C: Funded (will have a value or will be blank)

 

[Norie]

Why are you using INDIRECT?

 

[Gesyca_is_joy]

In my research it looked like I needed to have INDIRECT in my formula; but I can't get the formula to work with or without it in there.

 

[Gesyca_is_joy]

So i have worked on this some more and I have found a couple things:

1: If i use a conditional formula in parts I can get it to work. both of the formulas below give me a red cell when used. but when i try to put them together into a single formula I get no results.

• =AND($A2<>"",$M2="")
• =INDEX('Chart of Accounts'!$C$4:$C$1002,MATCH($H2,'Chart of Accounts'!$A$4:$A$1002,0))=""

 

[Gesyca_is_joy]

Answer, I figured it out!
=AND($A2<>"",$M2="",(INDEX('Chart of Accounts'!$C$4:$C$1002,MATCH($H2,'Chart of Accounts'!$A$4:$A$1002,0)))="")