Excel IF and AND formula?

[bfletcher]

I am sure this will be easy for the experts here but wondering how this can be done. Column A will have numerous codes that can also contain duplicates. Column B will have a series of years listed in it. So basically I want a formula to look into column A and it will look at the codes there...then look for a specific year in Column B (let's say 2021). When it finds the codes in column A...and identifies that at least one of those codes matches with the year 2021, it will identify ALL of those matching codes in Column A to be deleted. So that it would look something like this:

Perhaps there is a better formula to do this with. I was attempting to do an IF and AND formula and I could kind of get it to work but not quite. I apologize in advance if this is simplistic. I am still learning. Also to note, I am using Excel 2013. Thank you.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS(A:A,A2,B:B,$D$1),"del","")

 

[richh]

Hi bfletcher,

Welcome to the forum!

Here's a possible solution I slapped together real quick.

In Col C: =IF(B5=$F$4, 1, 0)
In Col D: =IF(SUMIF($A$5:$A$16,A5,$C$5:$C$16)>=1, "del", "")

Create a custom conditional formatting that will Highlight each row if D = "del"

 

[Fluff]

Why the helper column?

 

[richh]

Cause I'm dumb, lol.

 

[bfletcher]

Wow. You guys are fantastic and have to admit, I am feeling a little dumb. lol! So glad I found this resource and cannot wait to see what else I can learn (there's just so much to still learn(. Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[richh]

Happy to help!

Here's a screenshot of how to apply the custom conditional formatting, if needed. Change the cell reference to whatever first cell has the "del" in it. Be sure to only lock the Column reference in this condition so it can be drug down without error.