## Forum Discussion

# Excel - Nested formulas in conditional formatting - Nightmare!

I can not figure out how to conditionally format other rows that match the value highlighted in column D. Column D is formatted by Column AB containing "Not Loaded" - I need to highlight the other rows of data that contain any matching values of highlighted cells in Column D. Basically, I am running commission for about 100 salespeople. A new policy requires salespeople to upload photos of product taken in on trade to a site. If not loaded, no commission on original deal until completed. To take out the entire deal would be to remove the same invoice number matching with Column AB containing "Not Loaded" -

Please refer to screenshot for reference. I need to highlight the invoice number the green arrow is pointing to, and any other instances this occurs with different invoice numbers that are highlighted/Not Loaded. This data will be well over 20,000 rows in no time and I would like to come up with a solution while I'm still under 5k rows. You will be a lifesaver if you help me solve this!! Any and all comments will be appreciated! -SG

Add this conditional formatting in second position

=COUNTIFS($D$5:$D$

**9**;$D5;$AB$5:$AB$**9**;"Not Loaded")Adjust value 9

- HecatonchireIron Contributor
Add this conditional formatting in second position

=COUNTIFS($D$5:$D$

**9**;$D5;$AB$5:$AB$**9**;"Not Loaded")Adjust value 9

- mtarlerSilver ContributorTry Hecatonchire solution. I over complicated mine.
- mtarlerSilver Contributor

- ShainaDay88Copper ContributorI could not get this formula to work in conditional formatting - but I added a column with the COUNTIFS formula to return a value. Then, I added a new conditional formatting rule so if any value in the COUNTIFS column AD is greater than 0 - highlight it. Thank you so very much! I spent a ridiculous amount of time trying to figure this out!

- mtarlerSilver ContributorIf you post a sample sheet to work with we can better create a formula to help but I think you could use something like:

=ISNUMBER(XMATCH(D5, FILTER(D5:D10000, AB5:AB10000="Not Loaded","")))- ShainaDay88Copper Contributor
Let me know if this link doesn’t work. I’m on my phone and I don’t see anywhere to upload an attachment of the partial spreadsheet. I will check again when I get home to see if I can add an attachment. Can you see the screenshot? Thank you for attempting it!