troubleshooting Question

Tricky Microsoft Excel Matching Criteria

Avatar of Remote-IT™
Remote-IT™Flag for Australia asked on
Microsoft OfficeMicrosoft Excel
9 Comments1 Solution91 ViewsLast Modified:
I'm having a problem with getting a specific result with an Excel Formula.

As shown in the image below, I'm trying to match data from two Sheets (which represents data from two different sources. For the sake of the picture, the two sheets are shown together). The problem is that one sheet has multiple entries for the same Invoice #, where the other sheet has only one entry. What I need to be able to do is the following:
  1. If 'Value' from Sheet2 is greater than 0, and matches "Item C to match" entry in corresponding Invoice #, then 'pink' Conditional formatting does NOT Apply to any Item for corresponding Invoice # (See red rectangle). This is my problem I'm having, as it applies to items A, B & D which should not be highlighted in 'pink'. "Item C to match" here IS correctly NOT highlighted.
  2. If 'Value' from Sheet2 is greater than 0, and does NOT have a corresponding "Item C to match" entry for the corresponding Invoice #, then apply 'pink' conditional formatting to all items for that corresponding Invoice # (See green rectangle). This works correctly.
  3. If 'Value from Sheet2 is '0' and matches "Item C to match" entry for corresponding Invoice #, then apply 'pink' conditional formatting, but NOT for any item of the same corresponding Invoice #. This works correctly.

Tricky Matching Criteria

Current 'Conditional Formatting' Formula:
=IF(NOT(B1="Item C to match"),IF(INDEX(Sheet2!$C:$C,MATCH($A1,Sheet2!$A:$A,0))>0,IF(B1="Item C to match",0,1),0),IF(INDEX(Sheet2!$C:$C,MATCH($A1,Sheet2!$A:$A,0))>0,0,1))

I can understand why it applies the 'pink' highlight to Item A,B & D, because it does this correctly for Items G & H, but I need it to NOT apply when there is a "Item C to match" entry for the same corresponding Invoice #. This is what I find very tricky and can't seem to get my head around it.

The Conditional formula I need is how to Highlight any items if the Sheet2 'Value' is >0, but does NOT have the "Item C to match" item in it. If the "Item C to match" entry is in there for the corresponding Invoice #, then it must NOT highlight it or the other items. The image below has been manipulated manually to show desired results in red Rectangle.

Matching Criteria - Desired Results
A few things to point out:
  • For the 1st Image, the two Columns next to the Sheet1 Table is just to show how it matches up with the Value from Sheet2. The Conditional Formatting Column contains a test formula that reflects the numerical value that Conditional Formatting uses to highlight in 'pink'.
  • The "Item C to match" name does not change, but will always be the same. Items A-K can be in any order, duplicated or mixed, it doesn't matter.
  • The 'pastel' background colours are only to visually separate the Invoice batches and to show matching Invoices for Sheets 1&2. This is not part of, or required for the Conditional formatting.
  • Don't worry if the formula duplicates the Value from Sheet2 for the corresponding Invoice #. It doesn't matter. "Item C to match" is the most important item.
  • Please keep in mind, that the formula will be used in 'Conditional Formatting', so it needs to work for that purpose.
  • I've attached a sample Spreadsheet to make it easier for you to work on:
  • Sample-Tricky-Matching-Criteria.xlsx

Thank you for your assistance!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros