# Conditional Formatting using multiple criteria and INDEX MATCH

#### Gesyca_is_joy

##### Board Regular
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)

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Norie

##### Well-known Member
Why are you using INDIRECT?

#### Gesyca_is_joy

##### Board Regular
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

##### Board Regular
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

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

Replies
7
Views
243
Replies
2
Views
135
Replies
1
Views
91
Replies
7
Views
88
Replies
7
Views
115

1,136,969
Messages
5,678,883
Members
419,787
Latest member
juanam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back