Table of Contents
The ability to quickly and accurately find values in a column of an Excel spreadsheet is an essential skill for anyone working with data in a spreadsheet. Excel provides two primary methods for looking up values in a column: the VLOOKUP and INDEX-MATCH functions. Both methods are powerful tools for quickly finding values in a spreadsheet, and they are often used in conjunction with one another to achieve the desired results. In this article, we will discuss the benefits of each method, as well as how to use them. By the end of this article, you will have a better understanding of how to use these functions to effectively and efficiently find values in a column in Excel.
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.