Identifying if an entry is one of a group

You have a number of options that require the same treatment. What is the easiest way to identify if an entry is one of a list?

The COUNTIF function is useful in these types of situations. Let’s review the entries below.


We want to identify if an entry is column A is in the list in column D. Column B will display TRUE if the entry in column A is in the list in column D.

You may be tempted to use an OR function.Something like the formula below would work in cell B2


The problem with this formula is that if we need to add more entries to the list it means we have to modify the OR function to include any new entries.

The COUNTIF solution is more flexible. The formula for cell B2 is


This has been copied down the column.

As well as being much shorter, it allows you to add more entries at the bottom of column D to include them in the entries to identify.

The COUNTIF function counts how many times the criteria (the second argument in the function) is in the range (the first argument in the function). If another entry needs to be included you add it to the end of the list in column D and no change is needed to the COUNTIF formula.

You can use COUNTIF(D:D,A2)>0 as the logical test in an IF function if required eg

=IF(COUNTIF(D:D,A2)>0,"In List","Not in List")

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.