Conditional Formatting using multiple criteria and INDEX MATCH

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
79
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
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why are you using INDIRECT?
 

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
79
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
Joined
Apr 24, 2014
Messages
79
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
Joined
Apr 24, 2014
Messages
79
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)))="")
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top