Excel: How to Check if a Value Exists in a Range or List

A very common task you’ll encounter when working with large datasets is checking whether or not a value exists in a range or list of values. For example, you may want to see if a particular staff person checked in or whether an item was sold. In this tutorial, you’ll learn how to use Excel to check if a value exists in a range of values (or in a column).

By the end of this tutorial, you’ll have learned:

• How to use Excel to check if a value exists in a range using the COUNTIF() function
• How to use the MATCH() function and VLOOKUP() function to check if a value exists in a range of values

Using the Excel COUNTIF() Function to Check if a Value Exists in a Range

The Excel COUNTIF() function can be used to count values if they meet a certain condition. We can use this function to check whether or not a value exists by checking that its count value is greater or equal to 1. In order to make this happen, we’ll combine the COUNTIF() function with the IF() function.

Let’s take a look at how our workbook is set up:

We can see that we have a list of values that contain different websites. In cell D2, we want to return a “Yes” or “No” if the value in D2'thatexcelsite.com' exists.

Let’s write our formula:

=IF(COUNTIF(B2:B6), "thatexcelsite.com") > 0, "Yes", "No")

Let’s take a look at how this works:

I have broken the formula down into two parts:

1. The COUNTIF() part of the function counts a value if it matches our condition,
2. The second part checks whether the count of the value is greater than or equal to 1. If it is, it returns “Yes”. Otherwise, it returns “No”.

Hitting the Return key provides the following result:

In the next section, you’ll learn how to use the COUNTIF() function to check if a partial value exists in a column of values.

Check if a Partial Value Exists in a Range in Excel

We can use a very similar process to the one above to check if a partial value exists in a range of values. In order to do this, we can place some wildcard operators (*) into the COUNTIF() function.

This can be helpful when we don’t know how a string might be written. Similarly, you may be looking for a first name, when the last name doesn’t matter.

Let’s take a look at how we can modify our original function to check for a partial string. In the modified version below, we’re simply looking for the string 'thatexcelsite':

=IF(COUNTIF(B2:B6), "*thatexcelsite*") > 0, "Yes", "No")

Let’s see how this modifies our example in Excel:

Let’s break down what we’re doing:

1. The COUNTIF() part of the function counts a value if it matches our condition (even using a partial match),
2. The second part checks whether the count of the value is greater than or equal to 1. If it is, it returns “Yes”. Otherwise, it returns “No”.

Notice how the string is counted, even if it is missing the '.com' portion of the URL.

In the following section, you’ll learn how to use the Excel MATCH() function to check if a value exists in a column.

Using the Excel MATCH() Function to Check if a Value Exists in a Column

We can also use the Excel MATCH() function to check if a value exists in a column. The MATCH() function is used to search for a specific item in a range of cells and returns the relative position of that item. Because of this, we can use the MATCH() function to see if it doesn’t return a #N/A error.

Let’s see how we can combine the ISNUMBER() function and the MATCH() function to check if a value exists in a column.

= ISNUMBER(MATCH("thatexcelsite.com", B2:B6, 0))

Let’s take a look at what this looks like in our Excel workbook:

Let’s break down what the functions above are doing:

1. We use the MATCH() function to search through our range of values for "thatexcelsite.com",
2. If the function doesn’t return an error, it will return the relative position of the value. Then, the ISNUMBER() function will return TRUE. If the value isn’t found, the MATCH() function returns an error and the ISNUMBER() function returns FALSE.

In the final section below, you’ll learn how to use the VLOOKUP() function to check if a value exists in a list of values.

Using the Excel VLOOKUP() Function to Check if a Value Exists in a List

We can also use the Excel VLOOKUP() function to check if a value exists in a column. The VLOOKUP() function is used to search for a specific item in a range of cells and returns a value from an adjacent cell. Because of this, we can use the VLOOKUP() function to see if it doesn’t return a #N/A error.

Let’s see how we can combine the IFERROR() function and the VLOOKUP() function to check if a value exists in a column.

= IFERROR(VLOOKUP("thatexcelsite.com", A2:A10, 1, FALSE))

Let’s take a look at what this looks like in our Excel workbook:

We can see that we have two columns: one contains our websites and the other contains the number of visitors. Let’s break down what the functions above are doing:

1. We use the VLOOKUP() function to search through our range of values for "thatexcelsite.com",
2. If VLOOKUP() returns the number of visitors, then we know the value exists. We wrap this result in the IFERROR() function. If an error is raised, the VLOOKUP() function couldn’t find the value in the column. Then, the IFERROR() function will return “Doesn’t exist”.

This returns the following value:

Conclusion

In this tutorial, you learned how to use Excel to check if a value exists in a range of values. You first learned how to take on this task using the COUNTIF() function. Then, you learned how to use the MATCH() and VLOOKUP() functions in combination with the IFERROR() function. Being able to identify if values exist in a range of values allows you to more easily identify issues with your data.

Additional Resources

To learn more about related topics, check out the tutorials below:

• Excel: How to Remove Characters from Left or Right in a String
• Excel COUNTIF(): Official Documentation