asked on Tricky Microsoft Excel Matching Criteria
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:
![Tricky Matching Criteria]()
Current 'Conditional Formatting' Formula:
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:
Thank you for your assistance!
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:
- Sample-Tricky-Matching-Criteria.xlsx
Thank you for your assistance!
Microsoft ExcelMicrosoft Office
Last Comment
Remote-IT™
ASKER
Perfect! Thank you very much. I wish I'd asked earlier!
I've marked your answer as the solution, but I wouldn't mind if you could help me just with two additional criteria which I forgot to add.
Additional criteria required:
I've marked your answer as the solution, but I wouldn't mind if you could help me just with two additional criteria which I forgot to add.
Additional criteria required:
- If 'Value' in Sheet2 equals the text 'N/A' or has no value (blank), then "Item C to match" needs to be highlighted, but NO other item. Also, when the 'Value' is blank, it's actually not completely blank, it contains a formula that makes the results blank with "", so the function ISBLANK cannot be used here.
- The "N/A" will be actual text in the cell, not #N/A as a result of an error.
ASKER
Really sorry, but I've had to undo marking this as the solution, because I've marked it a bit too pre-maturely. I think I'm going to need an alternative formula. Although the formula with the COUNTIFS Function does exactly what I need, It adversely effects performance in Excel. I only noticed it after I tried to integrate it into my main spreadsheet which needs to handle a few hundred rows. I've modified the formula that was provided above to only go up to a 1000 rows, but it still slows excel down to a point where it almost freezes up.
Could you please try an alternative function that will produce the same results?
Thank you.
Could you please try an alternative function that will produce the same results?
Thank you.
Did you change the range for the CF also to $B$1:$B$1000.
1000 CF formulas working on only 1000 rows (5 times with the extra conditions) should not slow down Excel.
I don't have any delay.
On Sheet1 each Invoice row must be compared to all rows, and the only other function i know that can do that is sumproduct.
I don't think that is faster.
On Sheet2 with only one record per invoice, Index/match could be an alternative, but all rows must be searched if nothing is found, so I don't think that is faster.
Here is the formula with the 2 extra condition
1000 CF formulas working on only 1000 rows (5 times with the extra conditions) should not slow down Excel.
I don't have any delay.
On Sheet1 each Invoice row must be compared to all rows, and the only other function i know that can do that is sumproduct.
I don't think that is faster.
On Sheet2 with only one record per invoice, Index/match could be an alternative, but all rows must be searched if nothing is found, so I don't think that is faster.
Here is the formula with the 2 extra condition
=OR(AND(COUNTIFS($A$1:$A$1000,A1,$B$1:$B$1000,"Item C to match")=0,COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,">0")),AND(B1="Item C to match",OR(COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,0),COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,""),COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,"N/A"))))
ASKER
The updated formula is much better, but there still seems to be a lag in Excel when using it. (FYI, I have a fast PC with lots of RAM). Scrolling becomes jerky and typing is delayed. I'd like to see a version of this formula with INDEX and MATCH. In my main spreadsheet, I have a quite a number of INDEX and MATCH formulas and I don't have any problems with performance. I don't even restrict them to a 1000 records, but use absolute reference for entire columns. Some of them are even combined with nested IF statements. The COUNTIFS function definitely seem slower to me. The problem is, I need to use your formula for doing similar matches for other criteria as well, and it's only going to get slower. Another problem is, my customer I'm doing this spreadsheet for, has a slow PC, so I definitely don't want any performance issues. If possible, can you try and modify the formula to include as much of INDEX and MATCH as possible to see if that would help?
Thank you.
Thank you.
If statements could help, because some part of the formula are skipped if the condition is false.
Here is an option with index match, and 2 if statements to reduce the number of calculations.
Sample-Tricky-Matching-Countifs.xlsx
Here is an option with index match, and 2 if statements to reduce the number of calculations.
=OR(IF(COUNTIFS($A$1:$A$1000,A1,$B$1:$B$1000,"Item C to match")=0,INDEX(Sheet2!$C$1:$C$1000,MATCH(A1,Sheet2!$A$1:$A$1000,0),1)>0,0),IF(B1="Item C to match",OR(INDEX(Sheet2!$C$1:$C$1000,MATCH(A1,Sheet2!$A$1:$A$1000,0),1)=0,INDEX(Sheet2!$C$1:$C$1000,MATCH(A1,Sheet2!$A$1:$A$1000,0),1)="",INDEX(Sheet2!$C$1:$C$1000,MATCH(A1,Sheet2!$A$1:$A$1000,0),1)="N/A"),0))
=OR(IF(COUNTIFS($A$1:$A$1000,A1,$B$1:$B$1000,"Item C to match")=0,COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,">0"),0),IF(B1="Item C to match",OR(COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,0),COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,""),COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$C$1:$C$1000,"N/A")),0))
Sample-Tricky-Matching-Countifs.xlsx
ASKER
Thanks Ejgil, it's starting to be a bit better. It's really strange, Excel is very responsive with all my other formulas, but the moment I have one COUNTIFS function in it, it starts lagging. Then when I get rid of it, it's good again. But anyhow, it's much better than what it was.
Just one thing I've noticed with the 'INDEX / MATCH' code, it incorrectly highlights 'All' Items for the same Invoice # when 'N/A' is in the Value on Sheet 2 and 'Item C to match' is not in the list on Sheet1. Could you please see if you can correct this?
Just one thing I've noticed with the 'INDEX / MATCH' code, it incorrectly highlights 'All' Items for the same Invoice # when 'N/A' is in the Value on Sheet 2 and 'Item C to match' is not in the list on Sheet1. Could you please see if you can correct this?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you Ejgil, the formula works well. It's a shame the COUNTIFS function causes performance issues, but at least this formula does what I need.
PS. I may have a few more coming, so keep an eye out..
PS. I may have a few more coming, so keep an eye out..
No "Item C to match" in any rows with the Invoice# on Sheet1 AND Value > 0 for the Invoice# on Sheet2
Or
"Item C to match" in the row on Sheet1 AND Value = 0 for the Invoice# on Sheet2
The formula is
Open in new window
Sample-Tricky-Matching-Criteria.xlsx