Excel - Nested formulas in conditional formatting - Nightmare!

New Contributor

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




7 Replies
If 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","")))

Partial Comm Sample to Post

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!

best response confirmed by ShainaDay88 (New Contributor)

Add this conditional formatting in second position


=COUNTIFS($D$5:$D$9;$D5;$AB$5:$AB$9;"Not Loaded")


Adjust value 9




Try @Hecatonchire solution. I over complicated mine.
I 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!

@ShainaDay88  appears to work.  had to adjust the columns a bit:



I deleted my original formatting rule and it's working properly. Thank you so very much for your help! Made my day!