Home » Google Sheets » How to Check If Value Exists in Range in Google Sheets (4 Ways)
FormulasGoogle Sheets

How to Check If Value Exists in Range in Google Sheets (4 Ways)

Maruf Niaz Maruf Niaz August 16, 2023 3511 0

When working with a large volume of data, we may need to check if a value exists within a specified range. This can be time-consuming and have the potential of being error-prone. In such cases, Google Sheets provides some easy ways with which we can check if a value exists in range in Google Sheets.

A Sample of Practice Spreadsheet

Download this spreadsheet to practice yourself.

4 Simple Methods to Check If Value Exists in Range in Google Sheets

In this article, we will discuss 4 easy methods to check if a value exists in a range of cells in Google Sheets.

Method 1: Using COUNTIF Function

The COUNTIF function can be used to count the occurrences of a specific value in a range. We use the following dataset which has two lists with fruit names. We will see whether or not fruit from List 1 exists in List 2 using the COUNTIF function.

dataset used to check if value exists in range in google sheets with countif function

  • At first, go to the cell where you want to show the count. We go to cell C5 in our example.

select cell to input countif function

  • Then, insert the COUNTIF function.

input countif function to check if value exists in range in google sheets

  • After that, insert the range where you want to look up the value. We type E5:E13 in our example.

insert range for countif function

  • Then, type the criterion of the COUNTIF function that is the value you want to look up in the range. We type B5.

insert countif criteria

  • This is what the final formula looks like:
=COUNTIF($E$5:$E$13,B5)

fibnal formula used to check if value exists in range in google sheets with countif function

  • Finally, press ENTER to show the occurrence of value. If a value does not exist within the range, the formula will return 0. If it exists one time then the formula will return 1 and so on.

final result after using countif function to check if data exist in range

Method 2: Applying IF Function with COUNTIF Function

The COUNTIF function cannot show a specific message if a value exists within a range. It only shows the number of occurrences. We can use the IF function with the COUNTIF function to show a specific message if a value exists in a range.

The dataset that we used for this method is the same as the previous method. We will show if fruit from List 1 exists in List 2 and show a Yes message if it exists and a No message if it does not exist using the IF function and the COUNTIF function.

dataset used to check if value exist in range with countif and if function

  • At first, go to the cell where you want to show the message. We go to cell C5.

select cell to show result

  • Then, type the following formula:
=IF(COUNTIF($E$5:$E$13,B5),"Yes","No")

formula used to check if value exists in range in google sheets with countif and if function

Formula Explanation:

  • COUNTIF($E$5:$E$13,B5)

First, the COUNTIF function returns the occurrences of the values in the range E5:E13.

  • IF(COUNTIF($E$5:$E$13,B5),”Yes”,”No”)

Then, the IF function will show the Yes message if the value exists in the range E5:E13 otherwise, it will show the No message.

  • Finally, press ENTER to show a message if the fruit name from List 1 exists in List 2.

final result after applying if with countif

Read More: Highlight Cell If Value Exists in Another Column in Google Sheets

Similar Readings

Method 3: Utilizing MATCH Function with IF and ISERROR Functions

The MATCH function can be used with the IF and the ISERROR function to show if a value exists in a range. For this method, we use the same dataset that we used for the previous methods. We will show if fruit from List 1 exists in List 2 and show a Yes message if it exists and a No message if it does not exist using the IF and the ISERROR function in combination with the MATCH function.

dataset used to check if value exists in range in google sheets with match function

  • At first, go to the cell where you want to show the result. We go to cell C5 in our example.

select the cell where to show result

  • Then, type the following formula:
=IF(ISERROR(MATCH(B5,$E$5:$E$13,0)),"No","Yes")

final formula to check if value exists in range with match function

Formula Explanation:

  • MATCH(B5,$E$5:$E$13,0)

First, the MATCH function will find the value in the range E5:E13 and will show the row number of the table where it exists.

  • ISERROR(MATCH(B5,$E$5:$E$13,0))

Then, the ISERROR function will return a FALSE message if the MATCH function can find the value in the range E5:E13 otherwise, it will return a TRUE message.

  • IF(ISERROR(MATCH(B5,$E$5:$E$13,0)),”No”,”Yes”)

Finally, the IF function will return a No message when the ISERROR function returns a TRUE message and it will return a Yes message when the ISERROR returns a FALSE message.

  • Finally, press ENTER to show the result. The formula will return a Yes message if the value from List 1 exists in List 2 and a No message if the value does not exist in List 2.

final result after applying match function to check if value exists in range

Read More: How to VLOOKUP All Matches in Google Sheets (2 Approaches)

Method 4: Checking with VLOOKUP, IF, and IFERROR Functions

The VLOOKUP function can be used with the IF function and the IFERROR function to show if a value exists in a range. It can be done only for numerical values. The previous three methods can be used both for numerical and text values.

Consider the following dataset with Employee Id, Name, and their respective Sales Amount. We will show if an Employee Id exists in the table by using a formula using VLOOKUP, IF, and IFERROR functions.

dataset used to check if value exist in range with vlookup function

  • At first, go to the cell where you want to show the result. We go to cell C14 in our example.

select the cell where to show result

  • Then, insert the following formula:
=IFERROR(IF(VLOOKUP(B14,B5:D10,3,0),"Yes"),"No")

final formula to check if value exists in range with vlookup function

Formula Explanation:

  • VLOOKUP(B14,B5:D10,3,0)

First, the VLOOKUP function will look for the value in the range B5:D10 and will return a value from the 3rd column of the same range if it can find a match.

  • IF(VLOOKUP(B14,B5:D10,3,0),”Yes”)

Then, the IF function will return a Yes message if the VLOOKUP finds a match otherwise, it will return a #N/A error message.

  • IFERROR(IF(VLOOKUP(B14,B5:D10,3,0),”Yes”),”No”)

Finally, the IFERROR function will return a No message when the IF function returns a #N/A error implying that the value does not exist in range B5:D10.

  • Finally, press ENTER to show if the value exists in a range. We can see that the formula returns a Yes message as the value exists in the range.

final result after applying vlookup function to check if value exists in range

  • The formula will return a No message if the value does not exist in the range.

result after applying vlookup function to check if value exists in range

Read More: How to Use IFERROR with VLOOKUP Function in Google Sheets

Conclusion

In this article, we showed you 4 methods to check if a value exists in the range in Google Sheets. Keep practicing the methods that we have shown here for a better understanding of the concept. We hope this article was useful to you to help you.

Also, check out other articles on OfficeWheel to keep on improving your Google Sheets work knowledge.

Related Articles

Tags:

Maruf Niaz

Maruf Niaz

My Name is Niaz. As a regular Google Sheets user, I have a good understanding of the users' needs and bad experiences. In my articles, I will share my knowledge which I hope, will be helpful to you. My latest educational degree was BSc in Textile Engineering from Bangladesh University of Textiles.

We will be happy to hear your thoughts

Leave a reply

About OfficeWheel.com

OfficeWheel.com is a place where you can learn all about Google Sheets, Excel, Data Analysis, and other Office-related programs. We provide tips, how-to guides, and also provide solutions to your business problems.

OfficeWheel
Logo