Need help with formula

[new2excel__12]

I have a list of all of the members of our company, and I also have 7 other lists (2016-2022) of all members that renewed their membership each year. I need to go through each of these lists, compare it to the main list, create new columns for each year, and put "yes" for the people that renewed their membership in each year.

I tried conditional formatting, found duplicates, and manually put yes under each name, but is there a faster way? I want to avoid going through each list and putting yes under each duplicate. Thanks a lot in advance!!

 

[Joe4]

Welcome to the Board!

There are numerous ways to do, i.e. using formulas like VLOOKUP, XLOOKUP, INDEX/MATCH though I would probably prefer a simple COUNTIF formula with an IF.
So it would be structured something like this:

=IF(COUNTIF(...)>0,"Yes","")

If you are unfamiliar with COUNTIF, see here: MS Excel: How to use the COUNTIF Function (WS)
If you need help, please provide the layout details (i.e. ranges) of all your data and where the expected output should be (maybe some images).

 

[new2excel__12]

Welcome to the Board!

There are numerous ways to do, i.e. using formulas like VLOOKUP, XLOOKUP, INDEX/MATCH though I would probably prefer a simple COUNTIF formula with an IF.
So it would be structured something like this:

=IF(COUNTIF(...)>0,"Yes","")

If you are unfamiliar with COUNTIF, see here: MS Excel: How to use the COUNTIF Function (WS)
If you need help, please provide the layout details (i.e. ranges) of all your data and where the expected output should be (maybe some images).

Thank you so much for your quick response. Unfortunately, I wasn't able to figure it out on my own. I have attached an image to this reply. Would you please be able to help me with the formula? Thank you again so much for taking the time to respond.


In column C I have full names of all members of all times. In column D only people who renewed in 16-17, and in E, who renewed in 17-18.
I want to use a formula that will compare names in column D to column C and if there is a duplicate put YES under column F. Also have formula that will compare column E and C and if there are duplicates put YES in column G. Does this make sense?

 

[Joe4]

Place this formula in cell F2:

=IF(COUNTIF(D:D,$C2)>0,"Yes","No")

and then copy to the range F2:G4 and it should return the correct results.

 

[new2excel__12]

Place this formula in cell F2:

=IF(COUNTIF(D:D,$C2)>0,"Yes","No")

and then copy to the range F2:G4 and it should return the correct results.

Thank you so much for your reply. I really appreciate it! Unfortunately, it did not quite work, maybe I am doing something wrong? I have attached a screenshot of the first 3 people and E1 should say no instead of yes. Did I misinterpret the formula?

 

[Joe4]

This is what it shows for me:

The only way it should show a "Yes" in cell E5 is if you have the value "Betker Alexis" somewhere in column D. Are you sure you don't have it further down the column?

We could limit our formula to just the cells shown, as you should see it work then, i.e.

=IF(COUNTIF($D$2:$D$4,$C2)>0,"Yes","No")

It just isn't as dynamic then, as you may need to change the ending row numebr depending on how much data you have.

 

[new2excel__12]

This is what it shows for me:
View attachment 71918

The only way it should show a "Yes" in cell E5 is if you have the value "Betker Alexis" somewhere in column D. Are you sure you don't have it further down the column?

We could limit our formula to just the cells shown, as you should see it work then, i.e.

=IF(COUNTIF($D$2:$D$4,$C2)>0,"Yes","No")

It just isn't as dynamic then, as you may need to change the ending row numebr depending on how much data you have.

Oh yes! you are absolutely right. I found my mistake. Thanks so much! Really appreciate your help!! It would have taken me hours to do otherwise!!

 

[Joe4]

You are welcome.
Glad I was able to help.