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:
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.
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.
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.
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.
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:
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.
Our community of experts have been thoroughly vetted for their expertise and industry experience.