How to Find Value in Column in Excel (2 Methods)

Often you may want to find a specific value in a column in Excel.

There are two common ways to do so:

1. Highlight value in column.

2. Return “Yes” or “No” to indicate if value exists in column.

The following examples show how to use each method in practice.

Example 1: Find Value in Column in Excel (Highlight Value)

Suppose we have the following list of Employee ID’s in Excel:

Now suppose that we would like to find and highlight the value AB002 in this list of Employee ID’s.

To do so, we can highlight the cell range A2:A13, then click the Conditional Formatting icon on the Home tab, then click Highlight Cells Rules, then click Equal To:

In the new window that appears, type AB002 into the box called Format cells that are EQUAL TO, then choose a conditional format to use.

We will choose Light Red Fill with Dark Red Text:

Once we click OK, the cell that contains the value AB002 will be highlighted:

Note that if this Employee ID value didn’t exist then none of the cells would be highlighted.

Example 2: Find Value in Column in Excel (Return Yes or No)

Suppose we have a list of all Employee ID’s at some company and another list that contains the ID for people who are considered Star Employees:

We can type the following formula into cell D2 to check if the first Star Employee ID belongs in the list of all Employee ID’s:

=IF(ISNA(MATCH(C2, $A$2:$A$13, 0)), "No", "Yes")

We can then click and drag this formula down to each remaining cell in column D:

Column D now displays “Yes” or “No” to indicate if each value in the Star Employee list belongs in the total Employee ID list.

For example:

  • Employee ID AA004 exists in the total Employee ID list so the formula returns Yes.
  • Employee ID BA035 does not exist in the total Employee ID list so the formula returns No.

And so on.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: Check if Cell Contains One of Several Values
Excel: Check if Cell Contains Date
Excel: Calculate Average If Cell Contains Text

Leave a Reply

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