Excel - Nested formulas in conditional formatting - Nightmare!

[ShainaDay88]

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

[#2]

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

[#3]

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!

[#4 · Accepted solution]

Add this conditional formatting in second position

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

Adjust value 9

@ShainaDay88 

[#5]

Try @Hecatonchire solution. I over complicated mine.

[#6]

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!

[#7]

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

[#8]

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